Forum Discussion
Solved
New 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...
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
Hi GavinR87 ,
pivot the columns
https://docs.microsoft.com/en-us/power-bi/desktop-common-query-tasks#pivot-columns
Hi 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 🙂
