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. 🙂   tha...
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

     

     

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