Power Query - Re-merge a column

rossi789

New Member
Joined
Jul 30, 2015
Messages
30
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

Board Regular
Joined
Nov 2, 2017
Messages
82
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

New Member
Joined
Jul 30, 2015
Messages
30
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

Board Regular
Joined
Nov 2, 2017
Messages
82
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:

horseyride

Board Regular
Joined
Nov 2, 2017
Messages
82
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"
 

Forum statistics

Threads
1,082,478
Messages
5,365,789
Members
400,851
Latest member
FrankNStein

Some videos you may like

This Week's Hot Topics

Top