Grouping on merge tables

[michellerob2000]

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

[michellerob2000]

Im trying again to explain what Im trying to achieve below: hopefully it might help (got my fingers crossed)

[michellerob2000]

Sorry about that, see below what I'm trying to achieve (the latter)

[Anonymous]

Just to be sure you want this outcome from your merged table?

[michellerob2000]

Yes please from the  merge1 table.

Your script that you provided failed at the first table1 (I don't have table1 label anywhere - I tried changing it to merge1 but it still fails

[michellerob2000]

And I dont need the Level 1 and 2 - just the approver 1,2,3,4,5,etc

[Anonymous]

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"

[Anonymous]

Hi,

 Not sure what you are trying to achieve? Whats your desired output from the grouping?

Thanks,