Forum Discussion
Helper IINew columns from row data
Hi community,
Slightly more complex one that I am hoping someone can help with. Please bare with me and hopefully this makes some sort of sense!
We receive traffic data in CSV format.
The important columns of this data is date, time (minute averages), detector address, type, and value.
Now type can be one of a few things, the most common being either speed or flow. The value cell therefore is either the value of speed or flow, depending on what the adjacent "Type" column says.
Hope this makes sense so far.
So we have something a bit like this:
| Date | Time | Detector | Type | Value |
| 1/1/20 | 00:01 | 1234 | Speed | 80 |
| 1/1/20 | 00:01 | 4567 | Speed | 78 |
| 1/1/20 | 00:01 | 1234 | Flow | 20 |
| 1/1/20 | 00:01 | 4567 | Flow | 21 |
What I am looking to do is make new columns for speed and flow, and then lose the redundant rows, so my above example would end up like this:
| Date | Time | Detector | Speed | Flow |
| 1/1/20 | 00:01 | 1234 | 80 | 20 |
| 1/1/20 | 00:01 | 4567 | 78 | 21 |
As you can see I now have columns for speed and flow, and these values are now in single rows, which halves the amount of rows I had previously.
Now in Excel I could do this with a few ifs and get the result I want fairly easy. But I want to avoid having to manually manipulate the multiple CSV files per day, so would like to do this in Power BI somehow.
I am sure there must be a way.
As always grateful for any guidance here!
Cheers
Here's all the code you need (insert it into a blank query in PQ and execute):
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyUNJRMjCwMjAE0oZGxiZAKrggNTUFSFsYKMXqYFFmYmpmjqTM3AK7Mqhpbjn55UDKCL9hMFWGSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Time = _t, Detector = _t, Type = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", type time}, {"Detector", Int64.Type}, {"Type", type text}, {"Value", Int64.Type}}), #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Type]), "Type", "Value") in #"Pivoted Column"Best
D
4 Replies
- mwegener
Most Valuable Professional
Hi GavinR87 ,
pivot the columns
https://docs.microsoft.com/en-us/power-bi/desktop-common-query-tasks#pivot-columns
GavinR87Hi mwegener
Thanks so much for the reply.
I did the pivot and it sort of worked, but still not quite what I want. I more than likely did something a bit wrong!
So now I've gone from this:
Date Time Detector Type Value 1/1/20 00:01 1234 Speed 80 1/1/20 00:01 4567 Speed 78 1/1/20 00:01 1234 Flow 20 1/1/20 00:01 4567 Flow 21 To:
Date Time Detector Speed Flow 1/1/20 00:01 1234 80 Null 1/1/20 00:01 4567 78 Null 1/1/20 00:01 1234 Null 20 1/1/20 00:01 4567 Null 21 Is there an easy way to get from the above to:
Date Time Detector Speed Flow 1/1/20 00:01 1234 80 20 1/1/20 00:01 4567 78 21 Hope this makes sense, and again appreciate your time for answering 🙂
Here's all the code you need (insert it into a blank query in PQ and execute):
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyUNJRMjCwMjAE0oZGxiZAKrggNTUFSFsYKMXqYFFmYmpmjqTM3AK7Mqhpbjn55UDKCL9hMFWGSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Time = _t, Detector = _t, Type = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", type time}, {"Detector", Int64.Type}, {"Type", type text}, {"Value", Int64.Type}}), #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Type]), "Type", "Value") in #"Pivoted Column"Best
D
