Forum Discussion

rodg's avatar
rodg
Frequent Visitor
2 years ago
Solved

split column into matching columns

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

 

 

 

 

 

Need Help
  • ronrsnfld's avatar
    ronrsnfld
    2 years ago

    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

     

     

4 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    2 years ago

    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's avatar
      rodg
      Frequent Visitor
      2 years ago

      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's avatar
    lbendlin
    Super User
    2 years ago

    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.

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    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