Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
pharcoff
New Member

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

 

 

 

1 ACCEPTED SOLUTION
v-easonf-msft
Community Support
Community Support

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])

 

 

 

 

e3.png

 

2.expanded  custom column

e4.png

 

Here is a sample i made:

url: 

https://wicren-my.sharepoint.com/:u:/g/personal/michael_wicren_onmicrosoft_com/ES9xAySGoVtEklCnkJWc_... 

Annotation 2020-01-23 145701.png

 

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.

View solution in original post

3 REPLIES 3
danextian
Super User
Super User

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"




Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
v-easonf-msft
Community Support
Community Support

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])

 

 

 

 

e3.png

 

2.expanded  custom column

e4.png

 

Here is a sample i made:

url: 

https://wicren-my.sharepoint.com/:u:/g/personal/michael_wicren_onmicrosoft_com/ES9xAySGoVtEklCnkJWc_... 

Annotation 2020-01-23 145701.png

 

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.

rajulshah
Resident Rockstar
Resident Rockstar

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.

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.