How to group by a second ID column in Power Query-CodePudding

I have the following data in excel:

I want to group this data by the PartnerNumber like shown below:

I attempted to use PowerQuery by grouping by PartnerNumber, but it returns an error every time. Is there any way to group by the VendorNumber with a comma separating all the values?

I'm ok with doing it via Excel or PowerQuery, either way is fine.

CodePudding user response：

In powerquery, click select Partner Number, right click, Group by ..., New Column Name: VendorNumber, and then hit ok to use all other default options

In formula bar, change end of code after each, so that

each Table.RowCount(_), Int64.Type}})

becomes

each Text.Combine(List.Transform([VendorNumber], Text.From), ", "), type text}})

sample full code:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"PartnerNumber"}, {{"VendorNumber", each Text.Combine(List.Transform([VendorNumber], Text.From), ", "), type text}})
in #"Grouped Rows"

CodePudding user response：

You may try any one of the approaches as well, since I have mentioned in comment above, sharing in Answer as well, 4 Approaches -> 2 Approach using Power Query & 2 with Excel Office 365 Formulas,

Please follow the steps for Power Query

• Select any cell of the source,

• From Data Tab --> Click From Table/Range (Under Get & Transform Group)

Approach One

• Select the First Column i.e. PartnerNumber & press CTRL A

• From Transform Tab --> Click Data Type as Text

• From Home Tab --> Under Transform Group --> Click Group By,

• GroupBy PartnerNumber --> New Column Name -> Vendor_Number --> Operation --> SUM --> Press Ok

• In the formula bar change the List.Sum To Text.Combine and press Enter

• From Home Tab press Close & Load Drop down, from Import Data choose either Existing sheet or New Sheet and press Ok

Here is the M-Code

let
    Source = Excel.CurrentWorkbook(){[Name="Partnertbl"]}[Content],
    DataTypeText = Table.TransformColumnTypes(Source,{{"PartnerNumber", type text}, {"VendorNumber", type text}}),
    #"Grouped Rows" = Table.Group(DataTypeText, {"PartnerNumber"}, {{"Vendor_Number", each Text.Combine(([VendorNumber]),","), type nullable text}})
in
    #"Grouped Rows"

Approach Two

• The first two steps remains same as in Approach One,

• From Home Tab --> Under Transform Group --> Click Group By,

• GroupBy PartnerNumber --> New Column Name -> All --> Operation --> All Rows --> Press Ok

• From Add Custom Tab --> Under General Group --> Click --> Custom Column

• New Column Name --> Vendor_Number

• Custom Column Formula -->

=Table.Column([All],"VendorNumber")

• Press Ok

• Then Extract The Values From The Vendor_Number Column By Pressing The Dropdown and Click Extract Values

• Select Delimiter as your choice ( I took comma )

• Remove the column that not required and from Home Tab press Close & Load Drop down, from Import Data choose either Existing sheet or New Sheet and press Ok

Here is the M-Code

let
    Source = Excel.CurrentWorkbook(){[Name="Partnertbl"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"PartnerNumber"}, {{"All", each _, type table [PartnerNumber=number, VendorNumber=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Vendor_Number", each Table.Column([All],"VendorNumber")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Vendor_Number", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"All"})
in
    #"Removed Columns"

Approach Three

Using Excel Formulas applicable To O365 & Excel 2021

• Formula used in cell D10

=UNIQUE(Partnertbl[PartnerNumber])

• Formula used in cell E10

=TEXTJOIN(", ",,FILTER(Partnertbl[VendorNumber],D10=Partnertbl[PartnerNumber]))

Approach Four --> Office 365 & Excel 2021 Formula

• Formula used in cell A10

=LET(U,UNIQUE(A2:A5),
CHOOSE({1,2},U,BYROW(U,LAMBDA(A,
TEXTJOIN(", ",,(FILTER(B2:B5,A2:A5=A)))))))