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
Wing
Frequent Visitor

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

 

 

1 ACCEPTED SOLUTION
Wing
Frequent Visitor

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

4 REPLIES 4
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

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.

 

MFelix
Super User
Super User

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Wing
Frequent Visitor

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.

 

 

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.