cancel
Showing results for 
Search instead for 
Did you mean: 
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
Super User III
Super User III

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.