Group multiple columns except one

[IEPMost]

Hi guys,

is it possible to group two columns into a list and keep one as it is in one step (in Power Query)?

i.e.: I have three columns [Date], [Sales], [CustomerID]; in the next step I want [Sales] and [CustomerID] in one List as a new column and [Date] as the second column?

Hope you know what I mean!:)

Cheers

[camargos88]

Hi @IEPMost ,

It's possible having a list of tables with those 2 columns. However you can't use it on your visuals.

Can you explain more about what you are trying to achieve ?

[IEPMost]

Hi @camargos88 ,

thanks for your quick response!!

I want to transform two of the columns ([CustomerID] and [Sales]) into a third column, which should display a list. In the same step I want to keep the column [Date].

This is my Code (you can ignore the if condition):

= Table.Group(#"Changed Type", {"CustomerID", "Sales"}, {{"ERROR", each List.Transform(List.Numbers(1,Table.RowCount(_)), each if _ = 1 then "GO" else "Error"), type list}})

So how can I keep the column [Date] without grouping it with [CustomerID] and [Sales]?

Thanks a lot!!:)

[camargos88]

@IEPMost ,

Can you provide an example of the input and desired output ?

Thanks

[IEPMost]

@camargos88

yes sure!!

So here are more details: I have three columns [Date], [CustomerID] and [meter reader]; if the meter reader stops counting for a certain CustomerID, a further column should display "Error" otherwise "GO". Therefore I have this formula:

#"Grouped Rows" = Table.Group(#"Changed Type", {"Reglernummer", "Wert"}, {{"ERROR", each List.Transform(List.Numbers(1,Table.RowCount(_)), each if _ = 1 then "GO" else "Error"), type list}}),

#"Expanded ERROR" = Table.ExpandListColumn(#"Grouped Rows", "ERROR")

After this step the column [Date] disappears (I know why, but I don´t know how to fix it) and I have to know when (on which day) the meter reader stops counting. So here is how it looks before:

Date		CustomerID	meter reader
01.01.2020		3	1000
02.01.2020		3	1500
03.01.2020		3	1500
04.01.2020		3	1500
01.01.2020		5	200
02.01.2020		5	300
03.01.2020		5	400
01.01.2020		8	1000
02.01.2020		8	1100
03.01.2020		8	1100

and this should be my output (in my report it´s the same just without the date):

Date		CustomerID	meter reader	Status
01.01.2020		3	1000	GO
02.01.2020		3	1500	GO
03.01.2020		3	1500	ERROR
04.01.2020		3	1500	ERROR
01.01.2020		5	200	GO
02.01.2020		5	300	GO
03.01.2020		5	400	GO
01.01.2020		8	1000	GO
02.01.2020		8	1100	GO
03.01.2020		8	1100	ERROR

So how can I use my formula for showing me the meter reader, which stopped counting and at the same time keep my Date [Column]? I hope you can help me out!!

Thank you:)

[camargos88]

@IEPMost ,

Check the attached file. Let me know if you have any question.

I just created a new column:

let
 _customer = [CustomerID],
 _meterReader = [meter reader],
 _date = [Date] in

if Table.RowCount(Table.SelectRows(#"Changed Type with Locale", 
 each 
 [CustomerID] = _customer and 
 [Date] < _date and
 [meter reader] = _meterReader
 )) > 0 then "ERROR" else "GO"