Power Query - Re-merge a column

[rossi789]

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

 

[horseyride]

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?

 

[rossi789]

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.

 

[horseyride]

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

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"

 

[horseyride]

Or dynamic levels , the other version was set to 4

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"

 

[horseyride]

also if you are just doing a Bill of Material type, then see https://www.excelguru.ca/forums/sho...tures-that-exist-in-only-2-columns-in-the-db&

 

[rossi789]

also if you are just doing a Bill of Material type, then see https://www.excelguru.ca/forums/sho...tures-that-exist-in-only-2-columns-in-the-db&

Thanks. I will give that a read when I get home tonight.

In the meantime here is some sample data https://drive.google.com/open?id=1iPv3DSskeemQEGfVHjPgT0G7VQiKcvVZ