Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
koorosh
Post Partisan
Post Partisan

split to distinct columns

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_0-1646421543214.png

 

1 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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

12 REPLIES 12
mahoneypat
Employee
Employee

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.

 

mahoneypat_0-1646438487038.png

 

 

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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...

 

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.

 

mahoneypat_0-1646486290404.png

 

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

mahoneypat_1-1646486432523.png

 

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

 

Regards

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@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:

pstaggers_0-1652910594792.png

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

 

 

 

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

 

Regards

What command is highlighted line?

koorosh_0-1646488244282.png

 

@koorosh 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.