Forum Discussion

pharcoff's avatar
pharcoff
New Member
6 years ago
Solved

How to extract data into multiple columns from a single column of varying fields in data source?

I've got a data source table extracted from Workday in an Excel file. I want to be able to import it into Power BI, however one of the columns in the data source contains multiple types of data that ...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    Hi ,  pharcoff 

     

    You can  try follow step:

    1.create custom column

     

     

    =let 
    t = Table.SelectRows(Table.FromList(Lines.FromText([Column1]), Splitter.SplitByNothing(), null, null, ExtraValues.Error),each [Column1] <> ""),
    t2 = Table.TransformColumnTypes(Table.SplitColumn(t, "Column1", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),{{"Column1.1", type text}, {"Column1.2", type text}})
    in Table.PromoteHeaders(Table.Transpose(t2), [PromoteAllScalars=true])

     

     

     

     

     

    2.expanded  custom column

     

    Here is a sample i made:

    url: 

    https://wicren-my.sharepoint.com/:u:/g/personal/michael_wicren_onmicrosoft_com/ES9xAySGoVtEklCnkJWc_RwBhiW6F_TkeE6flFhm-KG23g?e=oo0KjK 

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

danextian's avatar
danextian
Super User
6 years ago

Hello pharcoff 

 

Assuming that your data is in a single cell, try this.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY6xCoNADEB/JQiFurRYB8FNROhSC9VNHeIZJHBG0avYv++pSLtkeLy8pCicuJ8MxCSGxhC8mx/BH4EUOyqllBcp4pmlDSF97qDlXkKImo6FJzOi4XlTs4GkgRgNtf34CeGBbFOCoggSTcqMrFBv5nsYNK93I9WRvbvCHBeIVq6wZs3GFg7vmiy2PRFYB2tNgAY8/wTnwL8EwQnsk+6RiPuGfptwz/JdtcN1quoL", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Data = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Data", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Data"),
    #"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each [Data] <> null and [Data] <> ""),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Filtered Rows", "Data", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Data.1", "Data.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Data.1", type text}, {"Data.2", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Data.2", Text.Trim, type text}, {"Data.1", Text.Trim, type text}}),
    #"Pivoted Column" = Table.Pivot(#"Trimmed Text", List.Distinct(#"Trimmed Text"[Data.1]), "Data.1", "Data.2")
in
    #"Pivoted Column"