question

MJohannes-5125 avatar image
0 Votes"
MJohannes-5125 asked emizhang-msft answered

Power Query Relationship Combined Table

Hey Guys!
I got this table in the power query editor with items, their quantits and their processes.
37058-6.png
I need to make it ready as a format like this:
37152-8.png
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".
37070-7.png
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 :)

power-query-not-supportedoffice-excel-itpro
6.png (6.0 KiB)
8.png (9.1 KiB)
7.png (39.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 avatar image
1 Vote"
Lz-3068 answered MJohannes-5125 edited

@MJohannes-5125

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


· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

There's a possible optimization on the 2nd option. Let's see if it does what you want first ...

0 Votes 0 ·

It says:
37222-9.png
This is the code:
37184-10.png


0 Votes 0 ·
9.png (7.2 KiB)
10.png (48.2 KiB)

The second one is working finde! Thank you very much. And I think I know why the first one didnt work. (I Changed the Process Names to the "Process.#" Format. If I would change that, your first one would work too. But the second looks much cleaner.

0 Votes 0 ·
Lz-3068 avatar image
0 Votes"
Lz-3068 answered

@MJohannes-5125

Assuming data in Excel Table1:

37153-msanswer.png

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



msanswer.png (25.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 avatar image
0 Votes"
Lz-3068 answered Lz-3068 commented

@MJohannes-5125

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

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I allready implemented you second solution. These changes are just perfomance driven right? If I leave it original there wouldnt be any changes in the output?

0 Votes 0 ·

Correct: Optimization and no change in the output

0 Votes 0 ·
AndreyVG-0212 avatar image
2 Votes"
AndreyVG-0212 answered AndreyVG-0212 edited

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,

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

emizhang-msft avatar image
0 Votes"
emizhang-msft answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.