Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |