New columns from row data

[GavinR87]

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

[Anonymous]

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 · Accepted solution]

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

[mwegener]

Hi @GavinR87 ,

pivot the columns

https://docs.microsoft.com/en-us/power-bi/desktop-common-query-tasks#pivot-columns

[GavinR87]

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 🙂