Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
IEPMost
Helper III
Helper III

Group multiple columns except one

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

5 REPLIES 5
camargos88
Community Champion
Community Champion

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 ?



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



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!!:)

@IEPMost ,

 

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

 

Thanks



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



@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 CustomerIDmeter reader
01.01.2020 31000
02.01.2020 31500
03.01.2020 31500
04.01.2020 31500
01.01.2020 5200
02.01.2020 5300
03.01.2020 5400
01.01.2020 81000
02.01.2020 81100
03.01.2020 8

1100

 

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

 

Date CustomerIDmeter readerStatus
01.01.2020 31000GO
02.01.2020 31500GO
03.01.2020 31500ERROR
04.01.2020 31500ERROR
01.01.2020 5200GO
02.01.2020 5300GO
03.01.2020 5400GO
01.01.2020 81000GO
02.01.2020 81100GO
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:)

@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"

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.