Thanks for replying to my solution email - and loved seeing similar elements in your solution! A few things I learned from your solution: 1) Adding an index to "fix" the sort rather than Table.Buffer - I'm with you on this, PQ can forget the sort. 2) Extracting a list from the grouped tables in the Data column - nice technique 3) Using nQual as a parameter to Text.SplitByDelimiter I'm loving these challenges - keep them coming!

@AccessAnalytic

2 жыл бұрын

Thanks Steve, great that you and everyone are taking part and watching the solutions

i was just looking for that solution for over a week!!!! thanks!!! great tutorial

@AccessAnalytic

Жыл бұрын

Glad to help 🙂

This was extremely helpful!

@AccessAnalytic

4 ай бұрын

I appreciate you taking the time to let me know you found it useful

As always I learn something new from you videos, and I thank you!

@AccessAnalytic

2 жыл бұрын

Cheers Rick, glad I'm helping out

Thank you Wyn 😊 Learning so much from you, as always! The second approach is my favorite one 🤯

@AccessAnalytic

2 жыл бұрын

Thanks Alejandra, yep it's more elegant and easier to implement in more complex scenarios

Awesome Wyn! Great demo. Thanks for the two examples. Learned a lot! Thumbs up!!

@AccessAnalytic

2 жыл бұрын

Thanks Wayne.

Thank you!! Congrats for the 10K, and for the new logo. That's an excellent channel!

@AccessAnalytic

2 жыл бұрын

Thank you Iván

Just found your challenges. A lot of fun. You can turn off the automatic changed type steps. Data>Get Data> Query Options>Data Load>Type Detection. You can do it for all workbooks (Global) or individual workbooks as needed.

@AccessAnalytic

2 жыл бұрын

Yep, cheers Matthew. For this demo it would have been good to turn it off

This is such a massive time saver! I am now interested in the M code!!! Thank you and keep up the good job.

@AccessAnalytic

11 ай бұрын

Cheers, getting into M opens a whole new world of options

Exactly what I needed! Thanks!

@AccessAnalytic

Жыл бұрын

Glad to help Darlene

Second approach is super cool. I knew about the first one having used it on lot of occasions but the second one is amazing. Thanks for sharing his. 👍👍👍👍

@AccessAnalytic

2 жыл бұрын

Great, we all keep learning. Thanks for leaving a great comment

BRILLIANT STUFF!!! Just what I needed...

@AccessAnalytic

4 ай бұрын

You’re welcome. Thanks for taking the time to to let me know it was useful

This helped a lot. Saved my time! Thanks.

@AccessAnalytic

Жыл бұрын

Great! Thanks for taking the time to leave a kind comment

Thanks so much, that was just what I was looking for!

@AccessAnalytic

Жыл бұрын

Great, thanks for taking the time to leave a kind comment

Great trick! It is amazing and interesting to follow you along!

@AccessAnalytic

2 жыл бұрын

Glad you’re finding it interesting 😀

Thank you Wyan. I learned a lot from your Vedios. 2nd method is more attractive and my favourite. Thank you sharing your valuable knowledge.

@AccessAnalytic

Жыл бұрын

You’re welcome Priya. Thanks for taking the time to leave a kind comment

Amazing, great, i liked. Thank you!

@AccessAnalytic

2 жыл бұрын

Thanks Luciano

Thank you so much. You're a life saver.

@AccessAnalytic

Жыл бұрын

You're welcome :)

Nice! I was on the right path (grouping) and lost track of time. Having said that, I learn by doing - I am going to do both steps tonight! Thanks for sharing!

@AccessAnalytic

2 жыл бұрын

You're welcome Tom, thanks

Marvelous ideas 💡💡

@AccessAnalytic

2 жыл бұрын

Thanks Sameh

Wow, very nice tricks. Thank you very much for sharing.

@AccessAnalytic

2 жыл бұрын

You’re welcome Bhavik

Great video. I liked your second approach using the nQuail name and replace the list hardecoded by the M code.

@AccessAnalytic

2 жыл бұрын

Thanks for watching and leaving a comment

Beautiful!

@AccessAnalytic

Жыл бұрын

Thank you

Thanks for the challenge, it was fun and educational, as Power BI should be 🙂

@AccessAnalytic

2 жыл бұрын

Cheers Ivan, thanks for taking part

Oooh I didn't realised you can use a number on the second argument of the splitcolumn function. What a game changer! 😁

@AccessAnalytic

Жыл бұрын

Glad it helps

Another gem of a video thank you Wyn. If you had just shown the first and left it there that would have been good enough but the 2nd approach, voila!

@AccessAnalytic

2 жыл бұрын

😀 I’ll be demoing a clever 3rd option that several people submitted in a week or two. All good learning for all of us

Really helpful. Thanks.

@AccessAnalytic

5 ай бұрын

You’re welcome. I appreciate you taking the time to let me know you found it useful

Thank you soo much for this video

@AccessAnalytic

Жыл бұрын

You’re welcome 😀

Thank you for saving me timess 🙏🙏

@AccessAnalytic

Жыл бұрын

You’re welcome thanks for taking the time to leave a kind comment

PQ forgot the sort order in my solution to this challenge. I had to sort again at a later step. Definitely still a thing. Thanks for the solution,Wyn

@AccessAnalytic

2 жыл бұрын

Thanks for confirming Grainne

Amazing, i haveto learn a lot to have your level

@AccessAnalytic

Жыл бұрын

Every year I say the same thing about future me 😀

Thank you!!

@AccessAnalytic

Жыл бұрын

You’re welcome Jennifer

Really nice, thanks.

@AccessAnalytic

2 жыл бұрын

No worries Zach

Hi Wyn- Great Vid, ** Edit the below is incorrect, I was mixing up with table.split function. however I learned something the other day about the splittextbydelimiter function, the last argument is optional. All you need to do is delete the list and your done, no need to do nQuals or anything fancy. Your formula at 11:19 is actually all that’s needed. Just leave the last part blank and it’s totally dynamic.

@AccessAnalytic

2 жыл бұрын

Thanks Martin, Sadly that doesn’t actually work properly, it just splits the first record dynamically by the number of delimiters it has. Any subsequent records with more delimiters don’t get split

@martyc5674

2 жыл бұрын

@@AccessAnalyticI stand corrected , it was the table.split function where this works without passing something to the last argument(and then becomes dynamic). I jumped the gun, and expected similar behaviour with this function. Let me see if I can leverage the table.split function to form a solution to this problem.

@martyc5674

2 жыл бұрын

So I tried this again today, and turns out I’m completely wrong. I just got lucky(read fooled!!) with the small bit of test data I was using, as it happened to have the most delimiters in the first record. So to my dismay the behavior Wyn highlighted is also true in the table.split function 🥲🥲

@AccessAnalytic

2 жыл бұрын

Thanks for letting folks know, I think I’ve been caught by this is the past, I may even have a video where I used it

Thank you very much

@AccessAnalytic

7 ай бұрын

You’re welcome

Really enjoyed that, loved the unpivot, pivot, I always get confused with that and it's a bit trial and error, I also forget about using the aggregations in grouping and tend to type everything in the editor , when it came to a dynamic column count I used; List.Max ( List.Transform( Custom2 [List of Lists], (_)=>List.Count(_)) )

@AccessAnalytic

Жыл бұрын

Thank you

Interesting problem to solve & I loved the solutions you offered. To make it more interesting ( or for use at work), add year in which the qualification was acquired. Employee can acquire more than 1 qualification in any year :)

@AccessAnalytic

2 жыл бұрын

Thanks for the suggestion Ravi

First approach is cool for me

@AccessAnalytic

2 жыл бұрын

Yeah I like it too, thanks Kayode

Your videos are really nice.👍 I Subscribed you just now🙂

@AccessAnalytic

2 жыл бұрын

That's great Krishna, thanks

Great 👍

@AccessAnalytic

Жыл бұрын

You're welcome

Great🙏🙏

@AccessAnalytic

2 жыл бұрын

Thankyou

Fix for : 4:55 (making a list) - try write only writting = [Data][Qualification]

@AccessAnalytic

11 ай бұрын

Yep, nice shortcut

my approach is Group then use ''text.combine'' then Split. it should be quicker =)

@AccessAnalytic

2 жыл бұрын

Feel free to submit a file or code Ryan

Thank you so much for this tutorial. I would like to learn that what is the main diffrence between List and Table?

@AccessAnalytic

2 жыл бұрын

You're welcome. A list is a collection of items {A,B,C} that looks like a column. A Table consists of rows and columns (records). A single column Table looks very much like a list It's multiple related columns that really separate a table from a list

It's great

@AccessAnalytic

2 жыл бұрын

Thanks Antony

@antonysovy5887

2 жыл бұрын

@@AccessAnalytic thanks to You for sharing all theese interesting videos.

@AccessAnalytic

2 жыл бұрын

You’re welcome

I would like to thank you for this video. I was struggling with this for a quite long time. Is there any way to support you? Or at least give you coffee?

@AccessAnalytic

Жыл бұрын

Greatly appreciated. Just let people know about the channel and that you found it useful! Thanks 🙏🏻

I want the same thing but the reverse, meaning I want to map multiple rows to a single column, if there is name and say salary of each month in a single row, I would like to have name then each month salary vertically in multiple rows, then the name of the next employee salary of each month vertically and so on...Can any one give me a hint of how to do that.

@AccessAnalytic

Жыл бұрын

Maybe unpivot? kzread.info/dash/bejne/d4eV0pippLmelqw.html

Hello Wyn thanks, Leads me to a question: How to get the qualifications in a sort order with columns per qualification? A Accountant Column, Plumber Column etc.

@AccessAnalytic

11 күн бұрын

A groupby and then add a grouped index. Paste this into the advanced editor to see how it works let // Summary: This query processes a table from Manually entered table (saved as Json, removes unnecessary columns, groups rows by 'Qual', adds an index column, pivots the table, and cleans up the final output. // Developed by: access analytic // Source: Load and decompress JSON data into a table Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQpJTUzOUIrViVZyQuE5A3mOycklYI4LkBOQU5oL5rgCOcH5OSmZqUVKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Qual = _t]), // GroupedRows: Group the table by 'Qual' column GroupedRows = Table.Group(Source, {"Qual"}, {{"Data", each _, type table [Name = nullable text, Qual = nullable text]}}), // AddedIndex: Add an index column to each grouped table AddedIndex = Table.AddColumn(GroupedRows, "Custom", each Table.AddIndexColumn([Data], "Index", 1, 1)), // KeptOnlyCustom: Select only the 'Custom' column KeptOnlyCustom = Table.SelectColumns(AddedIndex, {"Custom"}), // ExpandedCustom: Expand the 'Custom' column into 'Name', 'Qual', and 'Index' columns ExpandedCustom = Table.ExpandTableColumn(KeptOnlyCustom, "Custom", {"Name", "Qual", "Index"}, {"Name", "Qual", "Index"}), // PivotedQual: Pivot the table on 'Qual' column, transforming 'Name' values into respective 'Qual' columns PivotedQual = Table.Pivot(ExpandedCustom, List.Distinct(ExpandedCustom[Qual]), "Qual", "Name"), // RemovedIndex: Remove the 'Index' column from the pivoted table RemovedIndex = Table.RemoveColumns(PivotedQual, {"Index"}) in RemovedIndex

Can you please have a short video on splitting columns to rows with aggregated data, like sales amount. Thank you

@AccessAnalytic

4 ай бұрын

Can you expand on what the issue is you’re trying to solve?

@pthapa55

4 ай бұрын

Example: What is your email, I can send the sample in the email.@@AccessAnalytic Thank you

thanks but can we achieve the result as vice versa.

@AccessAnalytic

Жыл бұрын

Sure there’s almost always a way to do anything as long as there’s some logical pattern

🙌🙌🔥🔥🙏🙏

@AccessAnalytic

Жыл бұрын

😀

How do we extract get data from web using Power Query containing more data in a table than it appears on the websites. Such websites show tables in pages so you need to click page 2 for you to see the next content.

@AccessAnalytic

2 жыл бұрын

This video may give you some ideas on that kzread.info/dash/bejne/dI6pupezdJvZhaQ.html

that great WYN, HOW CAN WE SOLVE THE SAME PROBLEM IN EXCEL

@AccessAnalytic

Жыл бұрын

Not sure what you mean, this is in Excel

Wait, isn't this rows to columns?

@AccessAnalytic

8 ай бұрын

It;s a bit more involved than that, but you could like do some combination of UNQIUE and then FILTER and TOROWS to get a similar result

Great challenge My approach by Table.FromRows and Table.Combine let Source = Excel.CurrentWorkbook(){[Name="tblQualifications"]}[Content], Sorted = Table.Buffer(Table.Sort(Source,{{"Name", Order.Ascending}, {"Qualification", Order.Ascending}})), Grouped = Table.Group(Sorted, {"Name"}, {{"T", each Table.FromRows({{[Name]{0}}&[Qualification]} ,{"Name"}&List.Transform({1..Table.RowCount(_) }, each "Qualification."&Text.From(_))) }}), Combine = Table.Combine(Grouped[T]) in Combine

@AccessAnalytic

2 жыл бұрын

Thanks for joining in!