Reply
Frequent Visitor
Posts: 6
Registered: ‎09-11-2018
Accepted Solution

Reshaping a column of list values into a long format table

My source data table, when imported, has a table column containing a list of values, such as [Store_ID] in the example below:

 

[Store_Owner], [Store_ID]
Owner A, {10001, 10002, 10003}
Owner B, {10004}
Owner C, {10005, 10006}

 

 

How does one unlist the [Store_ID] values and reshape the data into a "long" format table, either during table import and/or using DAX, resulting in the following long table format:

 

[Store_Owner], [Store_ID]
Owner A, 10001
Owner A, 10002
Owner A, 10003
Owner B, 10004
Owner C, 10005
Owner C, 10006

 

 


Accepted Solutions
Frequent Visitor
Posts: 6
Registered: ‎09-11-2018

Re: Reshaping a column of list values into a long format table

The reply by MFelix led me to a more simplified solution:  In the Power Query Editor, use Split Column by Delimiter and Split into Rows (found in the Advanced options) instead of splitting into columns.

 

This transform operation directly produced the "long" format table I needed.

 

 

View solution in original post


All Replies
Super User
Posts: 2,258
Registered: ‎09-19-2016

Re: Reshaping a column of list values into a long format table

Hi @Wing,

 

On the query Editor do the following:

  • Remove the brackes using replace
  • Split column by delimiter using comma
  • choose all columns that are created and unpivot them
  • Them remove and rename the columns as you need.

See M language coding:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUao2NDAwMNRRAFFGEMq4VilWJ1rJCSZrAuE7w/imEGVmQOFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Store Owner" = _t, #"Store ID" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Store Owner", type text}, {"Store ID", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","{","",Replacer.ReplaceText,{"Store ID"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","}","",Replacer.ReplaceText,{"Store ID"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1", "Store ID", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Store ID.1", "Store ID.2", "Store ID.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Store ID.1", Int64.Type}, {"Store ID.2", Int64.Type}, {"Store ID.3", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Store Owner"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Store ID"}})
in
    #"Renamed Columns"

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Community Support Team
Posts: 2,979
Registered: ‎02-06-2018

Re: Reshaping a column of list values into a long format table

Hi @Wing,

 

For your requirement, dax may not achieve that, M query would be better.

 

Assuming that you have the table like this when you import data in Query Editor.

 

import.PNG

 

If you want to get the table like this.

 

[Store_Owner], [Store_ID]
Owner A, 10001
Owner A, 10002
Owner A, 10003
Owner B, 10004
Owner C, 10005
Owner C, 10006

 

You only need expand the list to new rows with click the option highlight in yellow.

 

Untitled.png 

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 6
Registered: ‎09-11-2018

Re: Reshaping a column of list values into a long format table

The reply by MFelix led me to a more simplified solution:  In the Power Query Editor, use Split Column by Delimiter and Split into Rows (found in the Advanced options) instead of splitting into columns.

 

This transform operation directly produced the "long" format table I needed.

 

 

Highlighted
Frequent Visitor
Posts: 6
Registered: ‎09-11-2018

Re: Reshaping a column of list values into a long format table

Hi Cherry,   I later realized my [Store_ID] column is just a text field with commas, and not a bona fide list (didn't initially recognize this as a Power BI newbie).

 

So I used Split Column by Delimiter and Split into Rows to get the "long" format table I needed.

 

I will keep your solution in mind in situations involving lists.

 

Thank you.