Forum Discussion
Group multiple columns except one
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"