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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
cgkas
Helper V
Helper V

How to split column with list in columns?

Hi, I have a column with text values, null values and others are lists of variable size for which I'm trying to split into columns.

 

This is the sample input

image.png

 

This is the sample I have.

 

 

 

let
    Source = Table.FromList(
    {
        [Col1 = 1, Data = "abc de"],
        [Col1 = 3, Data = {1,2,3}],
        [Col1 = 5, Data = "mhwu 5"],
        [Col1 = 2, Data = {"o","p","q","r","s"}]
    },
    Record.FieldValues,
    {"Col1","Data"}
),

#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

 

 

 

 

This is the output I'm trying to get, but in step #"Converted to Table", I'm getting this error

"We cannot convert a value of type Table to type List"

Thanks for any help

 

image.png 

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

Simple enough

 

 

let
    Source = Table.FromRecords(
        {
            [Col1 = 1, Data = "abc de"],
            [Col1 = 3, Data = {1,2,3}],
            [Col1 = 5, Data = "mhwu 5"],
            [Col1 = 2, Data = {"o","p","q","r","s"}]
        }
    ),
    #"Added Column" = Table.AddColumn(Source, "tb", each Table.FromRows(if Value.Is([Data], type text) then {{[Data]}} else {[Data]})),
    #"Expanded Data" = let cnt = List.Max(List.Transform(#"Added Column"[tb], Table.ColumnCount)) in Table.ExpandTableColumn(#"Added Column", "tb", List.Transform({1..cnt}, each "Column" & Text.From(_)))
in
    #"Expanded Data"

 

 

CNENFRNL_1-1672116779843.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

3 REPLIES 3
CNENFRNL
Community Champion
Community Champion

Simple enough

 

 

let
    Source = Table.FromRecords(
        {
            [Col1 = 1, Data = "abc de"],
            [Col1 = 3, Data = {1,2,3}],
            [Col1 = 5, Data = "mhwu 5"],
            [Col1 = 2, Data = {"o","p","q","r","s"}]
        }
    ),
    #"Added Column" = Table.AddColumn(Source, "tb", each Table.FromRows(if Value.Is([Data], type text) then {{[Data]}} else {[Data]})),
    #"Expanded Data" = let cnt = List.Max(List.Transform(#"Added Column"[tb], Table.ColumnCount)) in Table.ExpandTableColumn(#"Added Column", "tb", List.Transform({1..cnt}, each "Column" & Text.From(_)))
in
    #"Expanded Data"

 

 

CNENFRNL_1-1672116779843.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Hi @CNENFRNL. Thanks for the help. It works, but I'm kind of lost with your solution.

 

What does mean {{[Data]}} and {[Data]}?

 

Additionally, I see you used a block "let - in" and defined a variable inside the step #"Expanded Data". That is new for me. What are the rules to use or when to use another/nested "let - in" ? Thanks

adudani
Super User
Super User

Hi cgkas,

Steps taken:

1. Duplicated the initial query.

2. Have filtered out the List and the other values in one query.

3. In the other query, extracted the columns with any delimiter.

4. Split it into columns by the delimiter in step 3.

5. Appended the filtered query (Query1) on this Query 2.

6. Sorted it using a custom sorting order as per the image.

 

CODE:

1) (Query 1) Filtered Query
let
Source = Table.FromList(
{
[Col1 = 1, Data = "abc de"],
[Col1 = 3, Data = {1,2,3}],
[Col1 = 5, Data = "mhwu 5"],
[Col1 = 2, Data = {"o","p","q","r","s"}]
},
Record.FieldValues,
{"Col1","Data"}
),
#"Filtered Rows" = Table.SelectRows(Source, each ([Col1] = 1 or [Col1] = 5))
in
#"Filtered Rows"

 

2 ) Query 2 (Appended Query)
let
Source = Table.FromList(
{
[Col1 = 1, Data = "abc de"],
[Col1 = 3, Data = {1,2,3}],
[Col1 = 5, Data = "mhwu 5"],
[Col1 = 2, Data = {"o","p","q","r","s"}]
},
Record.FieldValues,
{"Col1","Data"}
),
#"Filtered Rows" = Table.SelectRows(Source, each ([Col1] = 2 or [Col1] = 3)),
#"Extracted Values" = Table.TransformColumns(#"Filtered Rows", {"Data", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Data", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Data.1", "Data.2", "Data.3", "Data.4", "Data.5"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Data.1", type text}, {"Data.2", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Data.1", "Data"}}),
#"Appended Query" = Table.Combine({#"Renamed Columns", Query1}),
#"Added Conditional Column" = Table.AddColumn(#"Appended Query", "colsort", each if [Col1] = 3 then 2 else if [Col1] = 2 then 4 else if [Col1] = 5 then 3 else [Col1], type any),
#"Sorted Rows" = Table.Sort(#"Added Conditional Column",{{"colsort", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"colsort"})
in
#"Removed Columns"

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

Helpful resources

Announcements
Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors