split to distinct columns

[koorosh]

Hello Experts,

How can get a table from the following so that the new table shows separate group columns and in each column, the name of users belongs to that group?

[koorosh]

https://docs.google.com/spreadsheets/d/1jUP5ULzM1Oyts_uGVOyY2f41Is4Y16jQ/edit?usp=sharing&ouid=11435...

https://drive.google.com/file/d/1rwxcXKohf5QjcJg8E06W3tcgIwkf8xcw/view?usp=sharing

[mahoneypat]

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below. Not sure this is the best way to structure your data though. Your example data was an image, so I made a few rows of my own to test it out.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUUo31Ek30kk3VorViVZyAgnAec5QaQjPBSpnohQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Groups = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Groups", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Groups], ",")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Groups"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
    #"Grouped Rows" = Table.Group(#"Expanded Custom", {"Custom"}, {{"All", each _[Name]}}),
    Custom1 = Table.FromColumns(#"Grouped Rows"[All], #"Grouped Rows"[Custom])
in
    Custom1

Pat

Pat

[koorosh]

Hello and thanks Pat. Please let me check your query. Meanwhile could you please check the following file and let me know where is my problem?

https://drive.google.com/file/d/1rwxcXKohf5QjcJg8E06W3tcgIwkf8xcw/view?usp=sharing

https://docs.google.com/spreadsheets/d/1jUP5ULzM1Oyts_uGVOyY2f41Is4Y16jQ/edit?usp=sharing&ouid=11435...

[mahoneypat]

I checked it with your data and it appears to work. However, why do you want to structure the data this way? Any analysis/visualization in Power BI will be easier with your data unpivoted.

This structure would be better (like at the #"Expanded Custom" step).

Pat

[koorosh]

Hi Pat, the query did not work for me, could you please send pbix file including my excel file as input source? 

Regards

[mahoneypat · Accepted solution]

I copied your data from the spreadsheet and put it in through the Enter Data approach. The query is below (and contains your data). You can create a blank query, open the Advanced Editor and then replace the text there with this.

The last two steps are customized. The #"Grouped Rows" keeps the Name column as a List (usually Group steps result in a table). The Custom1 step uses a function not used often called Table.FromColumns. It converts a list of lists into a table, and the 2nd parameter provides the column names as a list.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY5JDoNADAT/4nMfGJgAOYY1YckHRpz7/z8AW05G4lgld8kpyUsgDGAJVnIgSafiomjUK1XgA6zBxtygLl4EtuATDIX50VOOk09bozmP8uKd6+rNfbxyry/qM65+Fv9nwfzm79+q+69q9PWj2r47Tg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Groups = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Groups", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Groups], ",")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Groups"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
    #"Grouped Rows" = Table.Group(#"Expanded Custom", {"Custom"}, {{"All", each _[Name]}}),
    Custom1 = Table.FromColumns(#"Grouped Rows"[All], #"Grouped Rows"[Custom])
in
    Custom1

I am still curious what analyses/visuals you have planned. This data structure is likely not ideal.

Pat

[pstaggers]

@mahoneypat I think I have a similar type problem and was wondering if this technique could be adapted to work.  I have a table with the last column "FailureModeMatches" that looks like this:

The numbers come from an index tied to a particular failure mode.  I would like to generate a unique column for each index and label that column with the index number.  The content of each column would be either "Yes" or null.  In this example row 1 would have a "Yes" in column.index5 and in column.index36, but a null in all other columns.  There is no set number of added columns other than what the data prescribes (currently about 40 columns, but may not be 1...40, and may expand as the index grows.

Thank you,

Paul

[mahoneypat]

A similar approach could be applied where you use Text.Split to create a list from your text string. I would then expand those lists to new rows and keep the data unpivoted rather than create many new columns. It will make analysis easier and faster.

Pat

[koorosh]

Thanks Pat. You are right. Visualization will be easier with data unpivoted.

Regards

[koorosh]

What command is highlighted line?

[PijushRoy]

@koorosh 

You need to provide access to any users in google drive files

[koorosh]

https://drive.google.com/file/d/1rwxcXKohf5QjcJg8E06W3tcgIwkf8xcw/view?usp=sharing

https://docs.google.com/spreadsheets/d/1jUP5ULzM1Oyts_uGVOyY2f41Is4Y16jQ/edit?usp=sharing&ouid=11435...

Please let me know whether is right now?