Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape 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.
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
Solved! Go to Solution.
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:
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.
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"
Proud to be a Super User!
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:
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.
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.
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
88 | |
83 | |
76 | |
49 |
User | Count |
---|---|
145 | |
140 | |
109 | |
68 | |
55 |