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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

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"









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


Proud to be a Super User!









"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
Super User
Super User

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
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.