Another great tutorial full of excellent tips and techniques. Thanks for sharing!

@AccessAnalytic

10 ай бұрын

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.

@AccessAnalytic

10 ай бұрын

Thanks for taking part

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 !

@AccessAnalytic

10 ай бұрын

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 )

@paspuggie48

10 ай бұрын

@@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 :)

Nice tips, very good. Thanks!

I just learned about power BI, thanks for the video. Regards

@AccessAnalytic

10 ай бұрын

You’re welcome. Check out my getting started playlist : kzread.info/head/PLlHDyf8d156Wh6gpMGyQ4pZ72x3EQhv9P

It’s a good one. Thank you!

@AccessAnalytic

10 ай бұрын

You’re welcome 🙏🏼

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.

@AccessAnalytic

9 ай бұрын

Thanks for the suggestion. It’s a bit difficult to visualise. Feel free to send an example to info@accessanalytic.com.au

Superb as always 👍

@AccessAnalytic

10 ай бұрын

Cheers !

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: kzread.info/dash/bejne/fGmEsNWcZZjXZ5s.html

@AccessAnalytic

10 ай бұрын

Cheers

@AccessAnalytic

10 ай бұрын

What would the formula then be? Something like = Table.TransformColumns(Source, {}, Text.Split(_, " ") ) - but that doesn't seem to work

@AccessAnalytic

10 ай бұрын

I see that this would work = Table.TransformColumns(Source, {} , each try Text.Split(_," ") otherwise _ )

👍👍

👏👏👏

Some nice techniques. Hopefully I'll never have to use them! 😄

@AccessAnalytic

10 ай бұрын

😆 absolutely!

Do we need this try..otherwise? Text.Split output is always the list

@AccessAnalytic

10 ай бұрын

It failed for me without it

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}}),

@AccessAnalytic

8 ай бұрын

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/

@AccessAnalytic

8 ай бұрын

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

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)),"")) 🤗

@AccessAnalytic

10 ай бұрын

Thanks

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

@mienzillaz

10 ай бұрын

As I saw video now I see that I could let dynamic renaming go.

@AccessAnalytic

10 ай бұрын

Hi, I advertise the challenge on the community board here, on LinkedIn and twitter and our access analytic blog.

@mienzillaz

10 ай бұрын

@@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.