split column into matching columns

[rodg]

Hi, I'm looking for a way to split 1 column into multiple columns, where the columns should match across all rows. Sounds very abstract but I hope my screenshots make it clear. 🙂

thanks in advance for all the tips

Ronny

[dufoq3]

Hi @rodg, different approach:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nQxVNJRykvMTQXRpjpmSrE6IFEjqCiINtMx17FQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, comment = _t]),
    Ad_Age = Table.AddColumn(Source, "Age", each 
        [ a = List.Transform(Text.Split([comment], ","), Number.From),
          b = List.Accumulate(
                    { 1..12 },
                    Table.FromRecords({_}),
                    (s,c)=> Table.AddColumn(s, "Age" & Text.From(c), (x)=> if List.Contains(a, c) then 1 else 0, Int64.Type))
        ][b], type table),
    Combined = Table.Combine(Ad_Age[Age])
in
    Combined

[ronrsnfld · Accepted solution]

You can develop your desired result by

• Splitting the comment column by the comma into rows
• Create a list of all the desired new columns (Age1..Age12)
• Prefixing the numbers in the splitted comment column with the string "Age"
• Pivoting the table, but using All the desired column names instead of just the ones that are appearing in the Comments column.

M Code (in Advanced Editor)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nQxVNJRykvMTQXRpjpmSrE6IFEjqCiINtMx17FQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, Comment = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Name", type text}, {"Comment", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {
        {"Comment", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), 
        let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Comment"),
    
    #"All Column Names" = List.Transform({1..ColumnsToAdd}, each "Age" & Text.From(_)),
    #"Prefix with 'Age'" = Table.TransformColumns(#"Split Column by Delimiter",{{"Comment", each "Age" & _, type text}}),
    #"Pivoted Column" = Table.Pivot(#"Prefix with 'Age'", #"All Column Names", "Comment", "ID", List.Count)
in
    #"Pivoted Column"

 Results

[rodg]

So the solution was to create an extra list ("All Column Names") & use the list.count as the last argument to the Table.Pivot function.

Thanks @ronrsnfld 

[lbendlin]

Normally you would only unpivot your data and then let the data model (or the disconnected table) do the work for you.  Power BI is not Excel, don't try to treat it that way.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nQxVNJRykvMTQXRpjpmSrE6IFEjqCiINtMx17FQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, Content = _t]),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Content", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Content"),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Content", "Age"}})
in
    #"Renamed Columns"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.