Forum Discussion

GavinR87's avatar
GavinR87
Helper II
6 years ago
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...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
6 years ago
In Power Query you can get this even easier than in Excel. As already suggested - pivot the relevant columns. It's a one-click operation in PQ.

Best
D
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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