Power Query Relationship Combined Table

Question

Hey Guys!
I got this table in the power query editor with items, their quantits and their processes.

I need to make it ready as a format like this:

It is like a relationship table. Process1 and process 2 and their quanties and then a combined query with process 2 and process3 and so on. They need to be in the Columns "From" and "To".

My go to now is to make a query for every relationship und then combine all the querys at the end. But i cant figure out a wway to make it all in one query.

I really hope you can help me :)

Answer 1

@M, Johannes

On reflection...the following should be more efficient (still with Excel Table1 as data source

let  
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],  
    ChangedTypes = Table.TransformColumnTypes(Source,  
        {  
            {"Items", Int64.Type}, {"Quantity", Int64.Type}, {"Process 1", type text}, {"Process 2", type text},  
            {"Process 3", type text}, {"Process 4", type text}, {"Process 5", type text}  
        }  
    ),  
    MergedTables = List.Transform(  
        {1..List.Count(List.Select(Table.ColumnNames(ChangedTypes), each Text.StartsWith(_,"Process "))) -1},  
        each  
            let  
                TableFrom = Table.RenameColumns(  
                    Table.SelectColumns(ChangedTypes,{"Items","Quantity","Process "&Text.From(_)}),  
                    {"Process "&Text.From(_),"From"}  
                ),  
                TableTo = Table.RenameColumns(  
                    Table.SelectColumns(ChangedTypes,{"Items","Quantity","Process "&Text.From(_ +1)}),  
                    {"Process "&Text.From(_ +1),"To"}  
                ),  
                JoinedTables = Table.NestedJoin(TableFrom,"Items", TableTo,"Items", "TableTo", JoinKind.FullOuter),  
                ExpandedTo = Table.ExpandTableColumn(JoinedTables, "TableTo", {"To"})  
            in  
                Table.SelectColumns(ExpandedTo, {"From","To","Quantity"})  
    ),  
    CombinedTables = Table.Combine(MergedTables)  
in  
    CombinedTables  

Sample updated

Answer 2

Hi.
A variant.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        colNames = Table.ColumnNames(Source),
        set1 = List.Range(colNames, 2, List.Count(colNames) - 3),
        set2 = List.Range(colNames, 3),
        setMaker = (sets as list) as record => [select = sets & {"Quantity"}, rename = List.Zip({sets, {"From", "To"}})],
        tablePrepaer = (rec as record) => Table.RenameColumns(Table.SelectColumns(Source, rec[select]), rec[rename]),
        transformSet = List.Transform(List.Zip({set1, set2}), setMaker),
        tableSet = List.Transform(transformSet, tablePrepaer),
        result = Table.Combine(tableSet)
    in
        result

Regards,

Answer 3

@M, Johannes

Assuming data in Excel Table1:

One way:

let  
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],  
    ChangedTypes = Table.TransformColumnTypes(Source,  
        {  
            {"Items", Int64.Type}, {"Quantity", Int64.Type}, {"Process 1", type text}, {"Process 2", type text},  
            {"Process 3", type text}, {"Process 4", type text}, {"Process 5", type text}  
        }  
    ),  
    ReplacedNull = Table.ReplaceValue(ChangedTypes,null,"N/A",Replacer.ReplaceValue,  
        {"Process 1", "Process 2", "Process 3", "Process 4", "Process 5"}  
    ),  
    UnpivotedProcesses = Table.UnpivotOtherColumns(ReplacedNull, {"Items", "Quantity"}, "Process", "From"),  
    ReplacedNA = Table.ReplaceValue(UnpivotedProcesses,"N/A",null,Replacer.ReplaceValue,{"From"}),  
    GroupedProcess = Table.Group(ReplacedNA, {"Process"},  
        {"ProcessTable", each Table.RemoveColumns(_,{"Process"}),  
        type table [Items=nullable number, Quantity=nullable number, From=text]}  
    ),  
    MergedTables = List.Transform({1..Table.RowCount(GroupedProcess)-1},  
        (i)=>  
            let  
                TableFrom = List.First(Table.SelectRows(GroupedProcess, each [Process] = "Process " & Text.From(i))[ProcessTable]),  
                TableTo = List.First(Table.SelectRows(GroupedProcess, each [Process] = "Process " & Text.From(i +1))[ProcessTable]),  
                JoinedTables = Table.NestedJoin(TableFrom,"Items", TableTo,"Items", "TableTo", JoinKind.FullOuter),  
                ExpandedTo = Table.ExpandTableColumn(JoinedTables, "TableTo", {"From"},{"To"})  
            in  
                Table.SelectColumns(ExpandedTo, {"From","To","Quantity"})  
    ),  
    CombinedTables = Table.Combine(MergedTables)  
in  
    CombinedTables  

Corresponding sample available here

Answer 4

@M, Johannes

On the 2nd option that you ACCEPTED you can change step MergedTables as follow:

MergedTables = List.Transform(  
    {1..List.Count(List.Select(Table.ColumnNames(ChangedTypes), each Text.StartsWith(_,"Process "))) -1},  
    each  
        let  
            TableFrom = Table.RenameColumns(  
                Table.SelectColumns(ChangedTypes,{"Items","Quantity","Process "&Text.From(_)}),  
                {{"Items","ID"},{"Process "&Text.From(_),"From"}}  
            ),  
            TableTo = Table.RenameColumns(  
                Table.SelectColumns(ChangedTypes,{"Items","Process "&Text.From(_ +1)}),  
                {"Process "&Text.From(_ +1),"To"}  
            ),  
            JoinedTables = Table.Join(TableFrom,"ID", TableTo,"Items", JoinKind.FullOuter)  
        in  
            Table.SelectColumns(JoinedTables, {"From","To","Quantity"})  
)  

Changes:

• [Quantity] is no longer selected in TableTo as it wasn't actually required
• Renanimg [Items] as [ID] in TableFrom (+ the above change) allows doing a Table.Join instead of Table.NestedJoin. Consequently step ExpandedTo is no longer required

Hope this all makes sense

Answer 5

Hi,
Thanks for "Accept Answer" the reply of Lz-3068, this behavior will help other partners who research the similar problem can get more information from the correct result.