Forum Discussion
Grouping on merge tables
I am nearly there but not too sure on the grouping if it'll work, would appreciate your advice
Sub project code table
Member table
I have merged these tables and here is what I have
Group on Sub project
With App Group name and approver names
Thank you in advance
Michelle
7 Replies
Sorry about that, see below what I'm trying to achieve (the latter)
let
Source = Table.NestedJoin(Table1, {"Level 2"}, Table2, {"App Group name"}, "Table2", JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"App Group name", "Approver 1", "Approver 2"}, {"App Group name", "Approver 1", "Approver 2"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Table2",{"Sub Project", "App Group name", "Level 1", "Level 2", "Approver 1", "Approver 2"}),
#"Appended Query" = Table.Combine({#"Reordered Columns", #"Reordered Columns"}),
#"Sorted Rows" = Table.Sort(#"Appended Query",{{"Sub Project", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
SubProject = Table.AddColumn(#"Added Index", "Sub Project Blank", each if Number.IsOdd([Index]) then [Sub Project] else ""),
#"App Group Name" = Table.AddColumn(#"SubProject", "App Group name Blank", each if Number.IsOdd([Index]) then "" else [App Group name]),
#"Level 1" = Table.AddColumn(#"App Group Name", "Level 1 blank", each if Number.IsOdd([Index]) then "" else [Level 1]),
#"Level 2" = Table.AddColumn(#"Level 1", "Level 2 blank", each if Number.IsOdd([Index]) then "" else [Level 2]),
#"Approver 1" = Table.AddColumn(#"Level 2", "Approver 1 blank", each if Number.IsOdd([Index]) then "" else [Approver 1]),
#"Added Custom" = Table.AddColumn(#"Approver 1", "Approver 2 blank", each if Number.IsOdd([Index]) then "" else [Approver 2]),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Sub Project Blank", "App Group name Blank", "Level 1 blank", "Level 2 blank", "Approver 1 blank", "Approver 2 blank"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Sub Project Blank", "Sub Project"}, {"App Group name Blank", "App Group name"}, {"Level 1 blank", "Level 1"}, {"Level 2 blank", "Level 2"}, {"Approver 1 blank", "Approver 1"}, {"Approver 2 blank", "Approver 2"}})
in
#"Renamed Columns"
Im trying again to explain what Im trying to achieve below: hopefully it might help (got my fingers crossed)
