Power Query - Re-merge a column

rossi789

New Member
Joined
Jul 30, 2015
Messages
31
I am pretty new to Power Query so please be gentle!

I have a table with 2 column's from a database. The first column is a Parent recipe, the second column is a Child recipe. Recipes will be found in both columns so no unique records.

Example:

PR CR
A B
C B
D C
E C
F E
G C

I need to create what is effectively a "recipe tree" table which can be put in a pivot table. Recipes can appear multiple times in each column as they are modified along the way to create a finished product. It may also go through a different number of processes along the way.

The aim is to identify which finished products the starting recipe is used in.

The result I think I need for the above is something like:

R1 R2 R3 R4
B A
B C D
B C E F
B C G

How could I go about doing something like this? I have tried searching but can't find the keywords to unlock something like this.

Thanks in advance
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Maybe I am missing it .. can you explain the logic from the top to the bottom table?
I don't see any "recipes appearing multiple times in each column". It looks like each column only has one letter in it
B comes from A, so B...A... makes sense
B comes from C and C from {D,E,G} so how does that generate the next 3 rows of bottom table?
 
Upvote 0
The aim is to identify which base recipe generate the most revenue. Revenue figures are only available via Finished Product Codes.

In my example, A, D, F and G are finished products which all lead back to B therefore I would sum all revenue of those products to calculate the revenue recipe B generates.
 
Upvote 0
There is a List.Accumulate method, but this is recursive function method that generates what you asked for
What I really think you need is something else, but without examples of data with numbers attached that can be summed, I'll leave it up to you
Assuming source data in Table1 with header rows


Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    PR_List=List.Buffer(Source[PR]),
    CR_List=List.Buffer(Source[CR]),

Highest =  (t as text) =>
   let Spot = List.PositionOf( PR_List, t ),
    a1=CR_List{Spot},
    Spot2 = List.PositionOf( PR_List, a1 )
    in if Spot2 = -1 then a1 else a1 & "," & @Highest(a1),

    #"Added Custom" = Table.AddColumn(Source, "Custom", each  Text.Reverse(Highest([PR]))&","&[PR]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Finished", each List.PositionOf( CR_List, [PR] )),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Finished] = -1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"PR", "CR", "Finished"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4"})
in
    #"Split Column by Delimiter"
 
Last edited:
Upvote 0
Or dynamic levels , the other version was set to 4
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    PR_List=List.Buffer(Source[PR]),
    CR_List=List.Buffer(Source[CR]),

Highest =  (t as text) =>
   let Spot = List.PositionOf( PR_List, t ),
    a1=CR_List{Spot},
    Spot2 = List.PositionOf( PR_List, a1 )
    in if Spot2 = -1 then a1 else a1 & "," & @Highest(a1),

    #"Added Custom" = Table.AddColumn(Source, "Custom", each  Text.Reverse(Highest([PR]))&","&[PR]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Finished", each List.PositionOf( CR_List, [PR] )),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Finished] = -1)),
    #"Added Custom2" = Table.AddColumn(#"Filtered Rows", "Custom.1", each 1+List.Count(Text.PositionOf([Custom],",",Occurrence.All ))),
    newCol= List.Transform({0 .. List.Max(#"Added Custom2"[Custom.1])}, each "Col "& Text.From(_) ),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), newCol),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"PR", "CR", "Finished"})

in #"Removed Columns"
 
Upvote 0

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top