Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hello,
I receive third party reports weekly and one of the columns looks like this:
In order to use in my Power BI, I need to extract or split this column so each 'charge' has its own column. Additionally, I only want the numbers to appear in the column values, while the charge name in the header, like so:
I'm not sure how to combat this because (1) the charges can be in variable positions cell-to-cell (see ex rows 2/3), (2) not all cells have the same charges (some cells can have 4 charges while some have 2), and (3) the numbers I want to extract are varying in size (i.e. they can be 6, 5, or 4 characters long).
I am very new to Power Query, but this is roughly what I believe I need:
*For new column named "Oversized Surcharges"*
If [Charge Description] contains "Oversized", then extract the numbers between "Oversized" and the first "|" after "Oversized" (Starting from the right).
How can this be done in Power Query?
Hello,
let
Source = your_table,
f = (t as text) =>
[a = Splitter.SplitTextByDelimiter(" | ")(t),
b = List.Transform(a, (x) => Record.AddField([], Text.AfterDelimiter(x, " "), Text.BeforeDelimiter(x, " "))),
c = Record.Combine(b)][c],
d = Table.AddColumn(Source, "d", each f([Charge Description (Example)])),
e = List.Buffer(List.Distinct(List.Combine(List.Transform(d[d], Record.FieldNames)))),
g = Table.ExpandRecordColumn(d, "d", e)
in
g
User | Count |
---|---|
9 | |
8 | |
6 | |
6 | |
6 |