Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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 ?
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!!:)
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:)
@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"
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |