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 I want to extract into individual columns. For example, I want to have a column for "cost center", "spend category", "supplier", etc.

The issue is a couple-fold:

1) The fields in question aren't consistent from row to row. You can see two examples of this below.

2) The second is that when I tried to use the option to extract text between delimiters, it doesn't seem to recognize the line-feed character. Yet oddly I can split it out into individual columns if I use the Split Column function and use the line feed there.

Any suggestions on how to handle this data?

 

 

Cost Center: 123A Cost Center Name

Receiving: NO

Region: Administrative

Spend Category: Maintenance Electrical

Supplier: Acme Co

Tax Applicability: Supplier/Expense Taxable at 13% (73.77% Rec)

Tax Code: Supplier HST 13% (13%)

 

 

 

 

 

Corporate Credit Card Account: Visa - Employee Pcards

Cost Center: 234B Another Cost Center

Employee: John Smith

Expense Item: Maintenance Building

Region: Administrative

Spend Category: Maintenance Building

 

 

 

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.

3 Replies

  • rajulshah's avatar
    rajulshah
    Resident Rockstar
    6 years ago

    Hello pharcoff,

     

    Do you want to split data into rows or columns?

    Can you please provide your sample data with expected results? I could help you in a better way with that.

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    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"