+ Reply to Thread
Results 1 to 10 of 10

Power Query to transform current table

  1. #1
    Registered User
    Join Date
    08-14-2019
    Location
    Netherlands
    MS-Off Ver
    2019
    Posts
    36

    Power Query to transform current table

    Hello Experts!

    I need Your help, I've got table with many ID products.
    Please can some1 take a look and help me with query ?

    I need something like i've made manually in file ( table below )

    thanks in advance !
    Attached Files Attached Files
    Last edited by Vixx1; 11-11-2020 at 12:23 PM.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Power Query to transform current table

    Which table in the workbook is the 'before' and which is the 'after'?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    08-14-2019
    Location
    Netherlands
    MS-Off Ver
    2019
    Posts
    36

    Re: Power Query to transform current table

    Fixed, sorry

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Power Query to transform current table

    Please try

    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-14-2019
    Location
    Netherlands
    MS-Off Ver
    2019
    Posts
    36

    Re: Power Query to transform current table

    Hi Bo_Ry,

    First , thanks for picking it up, its almost works , but only for 1 product ID, if You add 1 more product ID, its multiplying columns with Yes No store fast delivery

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Power Query to transform current table

    Vixx1

    Why do you want/need the data in this format?

  7. #7
    Registered User
    Join Date
    08-14-2019
    Location
    Netherlands
    MS-Off Ver
    2019
    Posts
    36

    Re: Power Query to transform current table

    Hi Norie,

    It might look different but there is 41 colours of 1 product and for 13k items is 13k x 41 rows. I need they final file 1 row = 1 ID but dont know how to do it

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Power Query to transform current table

    The following is a formula based proposal:
    1. Produce a distinct list of Properties, as modeled in H38:H42, using: =IFERROR(INDEX(Table2[Property],MATCH(0,INDEX(COUNTIF(H$38:H38,Table2[Property]),,),)),"")
    2. Produce a distinct list of Product ID's, as modeled in J1:J5, using: =IFERROR(INDEX(Table2[Product ID],MATCH(0,INDEX(COUNTIF(J$1:J1,Table2[Product ID]),,),)),"")
    3. Produce a list of column headers, as modeled in K1:Y1, using: =IF(MOD(COLUMNS($A1:A1),5)=1,INDEX($H39:$H42,ROUNDUP(COLUMNS($A1:A1)/5,0)),IF(MOD(COLUMNS($A1:A1),5)=2,"Yes",IF(MOD(COLUMNS($A1:A1),5)=3,"No",IF(MOD(COLUMNS($A1:A1),5)=4,"store","fast delivery"))))
    4. Produce a row of Properties, as modeled in K38:Y38, using: =IF(MOD(COLUMNS($A1:A1),5)=1,K1,J38)
    5. Fill J2:Y5 using: =IF(SUMPRODUCT((Table2[[Yes]:[fast delivery]]="X")*(Table2[[Product ID]:[Product ID]]=$J2)*(Table2[[Property]:[Property]]=K$38)*(Table2[[#Headers],[Yes]:[fast delivery]]=K$1)),"X","")
    For future reference please put enough data in the file so that we can know formulas/code need to accommodate things like multiple ID's.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  9. #9
    Registered User
    Join Date
    08-14-2019
    Location
    Netherlands
    MS-Off Ver
    2019
    Posts
    36

    Re: Power Query to transform current table

    Hello JeteMc,

    this is what i wanted !! thanks You so much You're best !!!!

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Power Query to transform current table

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Power query - transform help
    By N3CREN in forum Excel General
    Replies: 0
    Last Post: 10-23-2020, 09:01 AM
  2. Power Query: Combine and Transform from folder
    By LUKE92 in forum Excel General
    Replies: 13
    Last Post: 06-26-2019, 12:34 AM
  3. [SOLVED] Power Query transform help
    By ierosadopr in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-09-2019, 05:25 AM
  4. Power Query (Get & Transform) on Mac
    By SHI.NL in forum Excel General
    Replies: 2
    Last Post: 05-18-2018, 09:24 AM
  5. [SOLVED] Power Query / Get & Transform filtering on text
    By SHI.NL in forum Excel General
    Replies: 5
    Last Post: 04-28-2018, 11:50 AM
  6. Replies: 3
    Last Post: 04-20-2018, 08:30 AM
  7. Get & Transform (Power Query) not showing certain tables
    By BamBamMoneyBags in forum Excel General
    Replies: 0
    Last Post: 03-22-2017, 12:22 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1