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

     

     

lbendlin's avatar
lbendlin
Icon for Super User rankSuper 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.