Power Query Challenge Bananas Split - Splitting multiple related records from multiple columns
Вставка
- Опубліковано 18 сер 2023
- The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. accessanalytic.com.au/
You can view and download all the solutions here
aasolutions.sharepoint.com/:f...
Thanks for taking part everyone!
Did you know I've written a book "Power BI for the Excel Analyst"?
pbi.guide/book/
Connect with me
wyn.bio.link/ - Навчання та стиль
Another great tutorial full of excellent tips and techniques. Thanks for sharing!
Thanks, you’re welcome
I tried my solution and it had too many steps. I'm amazed that solutions were discussed. There's been a lot of learning for me. Thank you.
Thanks for taking part
Nice tips, very good. Thanks!
Cracking stuff Wynn, well done !
Ya know, I just learned this after using PQ for 6 years >>>
After editing a Query inside the Editor and making changes/deleting applied steps etc and reloading data, sometimes I realise it's not what I wanted and need to revert back to how the Query was. For years I've always gone back into the Editor and re-applied the steps manually (which can be a pain if it is multiple steps with some fancy M code etc)
I never knew though that if you press CRTL+Z (undo) whilst in the worksheet it will convert the Query back to it's original state i.e. all it's applied steps prior to when the data was loaded into the worksheet.
I'm sorry if you all already knew this but I never did and it's just blown my mind !
Yeah one of the beauties of Excel Power Query is the undo stack is respected. Shout out to Gil Raviv and team for that ( I think )
@@AccessAnalytic yup, big shout out to whomever because for years I've been manually rebuilding my queries when now it's just a CRTL+Z :)
I just learned about power BI, thanks for the video. Regards
You’re welcome. Check out my getting started playlist : ua-cam.com/play/PLlHDyf8d156Wh6gpMGyQ4pZ72x3EQhv9P.html
It’s a good one. Thank you!
You’re welcome 🙏🏼
Superb as always 👍
Cheers !
👏👏👏
👍👍
I have a challenge you might work out:
Set Up:
1. Say I have a table loaded into Power Query with 65,000 rows of parts with Part Numbers, Descriptions and other columns.
2. The line items have a "Group" column that might be Bolts, Pipe, Gaskets, etc.
Challenge:
1. Just ONE group of parts has 3 question marks in the center of the part number that looks something like this: "ORIFICE PLATE, NPS 1/2, 1/8 THK, ???, CL 150, RF, PADDLE TYPE, 316 SS"
2. I need to replace the ??? with a series of sizes like "3/4", 7/8", 1", 1 1/4", etc. ONLY for the lines within that part group "Orifice"
* Currently, I filter to just that part group, create a list of the sizes, then expand to new rows so that every Orifice part number is duplicated with the full range of part numbers and sizes
* I have to set that up as a separate query and merge it back in with the rest of the parts from all the other groups.
There must be a better way to do this, where I can set up a list or parameter and call that data to replace those question marks if they exist on a line in the Orifice Group and expand to new rows with the full range of sizes.
My solution is working, but I feel like it was clunky.
Thanks for the suggestion. It’s a bit difficult to visualise. Feel free to send an example to info@accessanalytic.com.au
Some nice techniques. Hopefully I'll never have to use them! 😄
😆 absolutely!
For the transformcolumns function, I made a video that shows that if you leave the list of functions blank with { }, the next argument is a default function and will apply to all columns so you don't have to specify the columns names:
ua-cam.com/video/J5PNsc55q78/v-deo.html
Cheers
What would the formula then be? Something like = Table.TransformColumns(Source, {}, Text.Split(_, " ") ) - but that doesn't seem to work
I see that this would work
= Table.TransformColumns(Source, {} , each try Text.Split(_," ") otherwise _ )
Do we need this try..otherwise? Text.Split output is always the list
It failed for me without it
Solution with dynamic array formula:
=LET(
Column1,TOCOL(TEXTSPLIT(CONCAT(REPT(Table1[ID]&" ",
LEN(Table1[Type])-LEN(SUBSTITUTE(Table1[Type]," ",""))+1))," ",,1)),
Column2,TEXTSPLIT(ARRAYTOTEXT(Table1[Type]),,{" ";";"},1),
Column3,--TEXTSPLIT(ARRAYTOTEXT(SUBSTITUTE(Table1[Unit Cost],"$","")),,{" ";";"},1),
Column4,--TEXTSPLIT(ARRAYTOTEXT(Table1[Sell Price]),,{" ";";"},1),
IFNA(VSTACK({"ID","Type","Unit Cost","Sell Price"},HSTACK(Column1,Column2,Column3,Column4)),"")) 🤗
Thanks
Hi Wyn,
Could you please solve this challenge for me? It would be amazing if you can give some advice on this :D
I want to dynamically split this line into multiple columns based on a list of prefixes. For example the prefix list like this
- "fn|" is for a funnel,
- "mta|" is for an audience,
- "lc|" is for a location, etc.,
Then this line will be broken into columns with respective data:
Input:
970x250_HTML_Master_v2:fn|Convert_mta|brochure_dropoff_cta|RAQ_mod|New_Super-car_lc|Urban_dt|Day_col|No data_cod|No data_fea|v1
Results
- Funnel: Convert
- Audience: Brochure_dropoff
- CTA: RQA
- Model: New_Super-car
- Location: Urban
etc.,
Instead of manually defining the hard code for each column like below code, I want to dynamically break down the input into multiple columns based on the list of prefixes and their naming (for example, if later I provide a new prefix like "met|" for media type into my prefix list, then the code will automatically generate new a column and extract that data into that column)
Extract_datapoint =
Table.AddColumn(
Table.AddColumn(
Table.AddColumn(
Table.AddColumn(
Table.AddColumn(
Table.AddColumn(
Table.AddColumn(
Table.AddColumn(
Table.AddColumn(
Table.AddColumn(
Table.AddColumn(
Table.AddColumn(
Table.AddColumn(
Table.AddColumn(
Table.AddColumn(
Table.AddColumn(#"Inserted Week of Year",
"NSC", each Text.BetweenDelimiters([Creative Version], "nsc|", "|"), type text),
"Language", each Text.BetweenDelimiters([Creative Version], "lang|", "|"), type text),
"Ad_Size", each Text.BetweenDelimiters([Creative Version], "size|", "|"), type text),
"Ad_Format", each Text.BetweenDelimiters([Creative Version], "form|", "|"), type text),
"Model", each Text.BetweenDelimiters([Creative Version], "mod|", "|"), type text),
"Selected_Model", each Text.BetweenDelimiters([Creative Version], "mmod|", "|")
?? Text.BetweenDelimiters([Creative Version], "dmod|", "|"),
type text),
"Funnel", each Text.BetweenDelimiters([Creative Version], "fn|", "|"), type text),
"MME_TA", each Text.BetweenDelimiters([Creative Version], "mta|", "|"), type text),
"NSC_TA", each Text.BetweenDelimiters([Creative Version], "sta|", "|"), type text),
"CTA", each Text.BetweenDelimiters([Creative Version], "cta|", "|"), type text),
"Location", each Text.BetweenDelimiters([Creative Version], "lc|", "|"), type text),
"Week_Time", each Text.BetweenDelimiters([Creative Version], "wt|", "|"), type text),
"Day_Time", each Text.BetweenDelimiters([Creative Version], "dt|", "|"), type text),
"Color", each Text.BetweenDelimiters([Creative Version], "col|", "|"), type text),
"Color_Code", each Text.BetweenDelimiters([Creative Version], "ccod|", "|"), type text),
"Feature", each Text.BetweenDelimiters([Creative Version], "fea|", "|"), type text),
#"Extracted Text Before Delimiter" = Table.TransformColumns(Extract_datapoint, { {"Color_Code",
each Text.BeforeDelimiter(_, "_", {0, RelativePosition.FromEnd}), type text}, {"Color",
each Text.BeforeDelimiter(_, "_", {0, RelativePosition.FromEnd}), type text},
{"Day_Time", each Text.BeforeDelimiter(_, "_", {0, RelativePosition.FromEnd}), type text},
{"Week_Time", each Text.BeforeDelimiter(_, "_", {0, RelativePosition.FromEnd}), type text},
{"Location", each Text.BeforeDelimiter(_, "_", {0, RelativePosition.FromEnd}), type text},
{"CTA", each Text.BeforeDelimiter(_, "_", {0, RelativePosition.FromEnd}), type text},
{"NSC_TA", each Text.BeforeDelimiter(_, "_", {0, RelativePosition.FromEnd}), type text},
{"MME_TA", each Text.BeforeDelimiter(_, "_", {0, RelativePosition.FromEnd}), type text},
{"Funnel", each Text.BeforeDelimiter(_, "_", {0, RelativePosition.FromEnd}), type text},
{"Selected_Model", each Text.BeforeDelimiter(_, "_", {0, RelativePosition.FromEnd}), type text},
{"Model", each Text.BeforeDelimiter(_, "_", {0, RelativePosition.FromEnd}), type text},
{"Ad_Format", each Text.BeforeDelimiter(_, "_", {0, RelativePosition.FromEnd}), type text},
{"Ad_Size", each Text.BeforeDelimiter(_, "_", {0, RelativePosition.FromEnd}), type text},
{"Language", each Text.BeforeDelimiter(_, "_", {0, RelativePosition.FromEnd}), type text},
{"NSC", each Text.BeforeDelimiter(_, "_", {0, RelativePosition.FromEnd}), type text}}),
Hi, I'd recommend posting to one of the communities for some help
community.fabric.microsoft.com/t5/Power-Query/bd-p/power-bi-services
or
www.reddit.com/r/PowerBI/new/
or
techcommunity.microsoft.com/t5/excel/bd-p/ExcelGeneral
or
www.reddit.com/r/excel/
Something like this might give you a starting point ( GPT4 generated )
let
// Your original text
SourceText = "970x250_HTML_Master_v2:fn|Convert_mta|brochure_dropoff_cta|RAQ_mod|New_Super-car_lc|Urban_dt|Day_col|No data_cod|No data_fea|v1",
// List of delimiters
Delimiters = {"fn|", "mta|", "cta|","mod|","lc|"},
// Unique separator
Separator = "#SEP#",
// Function to replace delimiters with a unique separator
ReplaceDelimiters = (text as text, delimiters as list, separator as text) as text =>
let
ReplacedText = List.Accumulate(delimiters, text, (currentText, delimiter) => Text.Replace(currentText, delimiter, separator))
in
ReplacedText,
// Replace delimiters in the source text
ModifiedText = ReplaceDelimiters(SourceText, Delimiters, Separator),
// Split the text using the unique separator
SplitText = Text.Split(ModifiedText, Separator),
// Remove the first element which is the text before the first delimiter
RelevantTexts = List.Skip(SplitText),
// Verify that we have the correct number of elements to pair
TextsToPair = if List.Count(RelevantTexts) > List.Count(Delimiters) then List.FirstN(RelevantTexts, List.Count(Delimiters)) else RelevantTexts,
// Pair each text segment with its corresponding delimiter
PairedTexts = List.Zip({Delimiters, TextsToPair}),
// Convert to table
ResultTable = Table.FromRows(PairedTexts, {"Delimiter", "ExtractedText"})
in
ResultTable
Ugh. How can I get heads up about these challenges? I was late, but here's my approach.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Custom1 = List.Transform(Table.ToRows(Source), each List.Transform(_, each try Text.SplitAny(_," ") otherwise {_})),
Custom2 = Table.Combine(List.Transform(Custom1, each Table.FillDown(Table.FromColumns(_),{"Column1"}))),
Custom3 = Table.ToColumns(Table.DemoteHeaders(Table.Transpose(Table.FromList(Table.ColumnNames(Source))))),
Custom4 = Table.RenameColumns(Custom2,Custom3)
in
Custom4
As I saw video now I see that I could let dynamic renaming go.
Hi, I advertise the challenge on the community board here, on LinkedIn and twitter and our access analytic blog.
@@AccessAnalytic what's community board, I wanna in:) I gave up on linked.. let say it's not the same thing as it used to be.