SOLVED

Power Query Question

%3CLINGO-SUB%20id%3D%22lingo-sub-2716516%22%20slang%3D%22en-US%22%3EPower%20Query%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2716516%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ParasUdani_0-1630665901781.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F307917i23805911C5945D66%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22ParasUdani_0-1630665901781.png%22%20alt%3D%22ParasUdani_0-1630665901781.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EHello%20Everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20help%20me%20to%20get%20the%20result%20through%20Power%20Query.%20I%20need%20final%20output%20as%20per%20the%20above%20snip%20shared.%20On%20the%20right%2C%20I%20have%20couple%20of%20tables%20with%20input%20data.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2716516%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2716726%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2716726%22%20slang%3D%22en-US%22%3EI%20just%20answered%20a%20very%20similar%20question%20on%20%2Fr%2Fexcel%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fwww.reddit.com%2Fr%2Fexcel%2Fcomments%2Fpghbqm%2Fpower_query_adding_a_dynamic_list_to_a_table_of%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.reddit.com%2Fr%2Fexcel%2Fcomments%2Fpghbqm%2Fpower_query_adding_a_dynamic_list_to_a_table_of%2F%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2716816%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2716816%22%20slang%3D%22en-US%22%3EDidn't%20understand%20the%20steps%20given%20in%20the%20link.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2716852%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2716852%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1145786%22%20target%3D%22_blank%22%3E%40ParasUdani%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhich%20part%20were%20you%20unable%20to%20do%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3Equery%20from%20each%20table%3C%2FLI%3E%3CLI%3Eadd%20a%20custom%20column%20to%20each%20%22Custom%22%20%3D%201%3C%2FLI%3E%3CLI%3EMerge%20%22Dynamic%20List%20%22%20(%3Dfirst%20query)%26nbsp%3B%20with%20%22Table%22%20(%3Dyour%20second%20query)%20on%20%22Custom%22%3CBR%20%2F%3E-%20Left%20join%3CBR%20%2F%3E-%20expand%20%22Table%22%20column%3CBR%20%2F%3E-%20delete%20custom%20helper%20columns%3C%2FLI%3E%3C%2FUL%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

ParasUdani_0-1630665901781.png

Hello Everyone,

 

Can someone help me to get the result through Power Query. I need final output as per the above snip shared. On the right, I have couple of tables with input data. 

6 Replies
Didn't understand the steps given in the link.
best response confirmed by ParasUdani (Occasional Contributor)
Solution

@ParasUdani 

 

Which part were you unable to do?

 

  • query from each table
  • add a custom column to each "Custom" = 1
  • Merge "Dynamic List " (=first query)  with "Table" (=your second query) on "Custom"
    - Left join
    - expand "Table" column
    - delete custom helper columns
Thank you.

@ParasUdani 

As variant

let
    Source = Category,
    #"Added Custom" = Table.AddColumn(Source, "Store", each Store[Store]),
    #"Expanded Store" = Table.ExpandListColumn(#"Added Custom", "Store"),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Store",{"Store", "Category"})
in
    #"Reordered Columns"
Thank you