PowerQuery - Split Colums

[elliotstan]

Hi All,
I have a csv that lists a bunch of drivers in different report groups that are split by a comma in one column. I can use Power Query to easly split the groups into different columns however I need sort them into different columns as well.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Driver
[/TD]
[TD]ReportGroups
[/TD]
[/TR]
[TR]
[TD]Driver1
[/TD]
[TD][TABLE="width: 189"]
<colgroup><col width="189"></colgroup><tbody>[TR]
[TD="width: 189"]Milk, Logs, Powder[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Driver2
[/TD]
[TD][TABLE="width: 189"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Powder, Haul, Sand, [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Driver3
[/TD]
[TD][TABLE="width: 189"]
<colgroup><col width="189"></colgroup><tbody>[TR]
[TD="width: 189"]Milk, Logs, Haul, Powder[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 253"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Is there any way power query can also sort the columns so each different value will appear in the correct column like below?

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Driver
[/TD]
[TD]ReportGroup1
[/TD]
[TD]ReportGroup2
[/TD]
[TD]ReportGroup3
[/TD]
[TD]ReportGroup4
[/TD]
[TD]ReportGroup5
[/TD]
[/TR]
[TR]
[TD]Driver1
[/TD]
[TD]Milk
[/TD]
[TD]Logs
[/TD]
[TD]Powder
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Driver2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Powder
[/TD]
[TD]Haul
[/TD]
[TD]Sand
[/TD]
[/TR]
[TR]
[TD]Driver3
[/TD]
[TD]Milk
[/TD]
[TD]Logs
[/TD]
[TD]Powder
[/TD]
[TD]Haul
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]







Cheers in advance for your help.

ED.

 

[Matt Allington]

Wild you mind explaining why? could you put the text actually in he header and 1 where the text is? Ie instead of having ReportGroup1 has he column heading, have "milk" as the heading and then 1 in each cell that currently has milk? You could I pivot the original data (after the split), then sort, then pivot the data and use count.

Just a guess actually - I haven't tried it.

 

[elliotstan]

Hi Matt,
Thanks for the help - that does sound like a solution that could work. Makes sense and easy to build reports on it.

How do I do that though? I can't seem to work out how to sort it like that as the different values can exist at different points in the ReportGroup column.
Cheers for the help.

ED

 

[Matt Allington]

How about this? I produced a quick video demo for you https://www.youtube.com/watch?v=v8ccS5N6xFk

 

[billszysz]

Hi Matt and elliotstan

Matt, we do not need prepare a csv file to import to PQ.
We can split column "ReportGoups" by delimiter ", " and force column count value (advanced option in split column window) to bigger than we expect.
The rest is the same as you showed.
Of course if elliotstan needs exactly the same output data setup as he showed in first post we can do that also. (See attachement - change the path to drivers2.csv file).
https://drive.google.com/file/d/0B6UlMk8OzUrxa1NORUdwWTdNWm8/view?usp=sharing

Regards

 

[elliotstan]

Wow again! Thanks billszysz - great to see the files and know that there is another way to do this. I had not even thought of processing it that way. Sure I can sort this issue now with these options. Cheers.

 

[elliotstan]

Hmm - Had to come back to this one as I am now connecting to a database with the column called ReportGroup containing the different values (deliminated with a comma) - so cant unpivot it and repivot it.

billszysz - I think you are right in that the column need to be split into more columns - I have set it for 20 as this will allow the number of report groups to increase.

Once split how do I get the data sorted though?

Original Database Data
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Truck
[/TD]
[TD]ReportGroups
[/TD]
[/TR]
[TR]
[TD]Truck001
[/TD]
[TD][TABLE="class: grid, width: 205"]
<colgroup><col></colgroup><tbody>[TR]
[TD]All Sites, All Vehicles, Transport Vehicles, Support1990, TFU
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Truck002
[/TD]
[TD][TABLE="class: grid, width: 205"]
<colgroup><col></colgroup><tbody>[TR]
[TD]10 Hours, All Sites, All Vehicles, Blue Driver Key Upload, DAF, TrialGForce
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Step 1 - I gather this would be the first step - splitting the values from the Original ReportGroups column by deliminator which I have setup.

[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD]Truck
[/TD]
[TD]ReportGroup1
[/TD]
[TD]ReportGroup2
[/TD]
[TD]ReportGroup3
[/TD]
[TD]ReportGroup1
[/TD]
[TD]
and so on
[/TD]
[/TR]
[TR]
[TD]Truck001
[/TD]
[TD]All Sites
[/TD]
[TD]All Vehicles
[/TD]
[TD]Transport Vehicles
[/TD]
[TD]Support1990
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Truck002
[/TD]
[TD]10 Hours
[/TD]
[TD]All Sites
[/TD]
[TD]All Vehicles
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Step 2 / What I want - only show a 1 if the vehicle is in that group. This is the next step - how to get a 1 value into the correct column.

[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD]Truck
[/TD]
[TD]AllSites
[/TD]
[TD]AllVehicles
[/TD]
[TD]TransportVehicles
[/TD]
[TD]Support1990
[/TD]
[TD]and so on
[/TD]
[/TR]
[TR]
[TD]Truck001
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Truck002
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Cheers in advance for some more help on this.
Also - I am trying to do this in PowerBI - not power query. So idea is I can load this up in the query editor first. Cheers.

 

[billszysz]

Hi elliotstan
First step is not needed - a small modification of one of query from my file will be enough. Here is a code.

let
    Source = Csv.Document(File.Contents("PathToYourCsvFile"),[Delimiter=";",Encoding=1250]),
    PromHead = Table.PromoteHeaders(Source),
    ChType = Table.TransformColumnTypes(PromHead,{{"Truck", type text}, {"ReportGroups", type text}}),
    LstOfRepGr = List.Sort(List.Distinct( Splitter.SplitTextByDelimiter(", ")(Text.Combine(ChType[ReportGroups], ", ")))),
    Tbl = Table.AddColumn(ChType, "Custom", each Table.FromRows({List.Transform(Text.Split([ReportGroups], ", "), each 1)}, Text.Split([ReportGroups], ", "))),
    ExpCol = Table.ExpandTableColumn(Tbl, "Custom", LstOfRepGr, LstOfRepGr),
    RemCol = Table.RemoveColumns(ExpCol,{"ReportGroups"})
in
    RemCol

Regards