How to use Pivot in Power Query and avoid the errors (PLUS bonus at minute 6)

No video

How to use Pivot in Power Query and avoid the errors (PLUS bonus at minute 6)

Рет қаралды 10,955

Access Analytic

Күн бұрын

Пікірлер: 83

@AgulloBernat Жыл бұрын

awesome

@AccessAnalytic Жыл бұрын

Cheers Bernat

@Looii5 Ай бұрын

The tips are great and so is the guitar music at the end! 🎸

@AccessAnalytic Ай бұрын

Cheers!

@kebincui Жыл бұрын

Excellent tip to deal with annoying error in pivoting a column. Thank you Wyn

@AccessAnalytic Жыл бұрын

You’re welcome Kebin

@krishanphophalia9303 Жыл бұрын

Great tip, Wyn! You always pleasantly surprise us with these weekly tips. ❤

@AccessAnalytic Жыл бұрын

Thanks Krishan 🤗

@sebastianschick1989 5 ай бұрын

very helpful, you've saved my day - thanks a lot, and the bonus tip was very helpful as well👍👌😊

@AccessAnalytic 5 ай бұрын

Glad to help

@zahoorsarbandi2982 Жыл бұрын

Believe me today I needed it and fortunately I found my solution in this video.. thank you very much sir

@AccessAnalytic Жыл бұрын

You’re very welcome

@abdulsaidmkamara7305 Жыл бұрын

always a pleasure learning from you on Saturday.

@AccessAnalytic Жыл бұрын

😀glad to teach ( and learn ) Abdul

@rahulkalingeri1206 Ай бұрын

Wowww.. just what I needed ..thanks a lot 🙂

@AccessAnalytic Ай бұрын

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

@MoRo9083 6 ай бұрын

Sir, usted no sabe como lo amo en este momento!

@AccessAnalytic 6 ай бұрын

Glad it was useful

@BillSzysz1 Жыл бұрын

Thanks for great lesson 👍. But i have one small remark. If the source table is not sorted by month then the order of the rows in the result table may be incorrect. So, i propose add as a first step of query (it doesn't have to be the first step, but for clarity, I like it to be at the beginning) MonthsOrder = Table.FromColumns( { List.Transform( List.Dates(#date(1, 1, 15), 12, #duration(30, 0, 0, 0)), each Date.ToText(_, "MMM", "en-GB") ), {1 .. 12} }, {"Months", "Idx"} ) Then the "Merged Queries" step and the following would look like this below #"Merged Queries" = Table.NestedJoin( ReadyForMerge, {"Month"}, MonthsOrder2, {"Month"}, "MonthsOrder", JoinKind.LeftOuter ), Expanded = Table.ExpandTableColumn( #"Merged Queries", "MonthsOrder", {"Idx"}, {"Idx"}), #"Sorted Rows" = Table.Sort( Expanded, {{"Idx", Order.Ascending}, {"Index", Order.Ascending}} ), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows", {"Index", "Idx"}) in #"Removed Columns" The advantage of this solution is that we can have the names of the months in any language (just change "en-GB" to, for example, "pl-PL")

@AccessAnalytic Жыл бұрын

Nice suggestion Bill

@TopBam 6 ай бұрын

Very useful! I needed this for a two-column data set as an alternative to my complicated let/drop/reduce/lambda formula.

@AccessAnalytic 6 ай бұрын

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

@dougmphilly 3 ай бұрын

anything is better than lambda. you hand offv a spreadsheet to anyone with that function all you get is stares.

@TopBam 3 ай бұрын

@@dougmphilly I still love LAMBDA! Greatest thing to hit excel since power query came out for Excel 2010. It is a real game changer, just like power query.

@dougmphilly 3 ай бұрын

@@TopBam its only intuitive to the most powerful of power users. i tried explaining the function in a shared spreadsheet that went absolutely nowhere.

@TopBam 3 ай бұрын

@@dougmphilly They can learn if they are taught correctly. Lack of competence is problem in every profession. Just because people don't understand it, doesn't mean we should not use it.

@user-en3pc7ko8e 5 ай бұрын

Awesome! Very powerful.

@AccessAnalytic 5 ай бұрын

Absolutely

@shubhabratadey 10 ай бұрын

Nice video. Many thanks

@AccessAnalytic 10 ай бұрын

You’re welcome

@ksowjanya4488 10 ай бұрын

Thank you for clear explanation.

@AccessAnalytic 10 ай бұрын

You're welcome

@behzadghah Жыл бұрын

grate solution. thank you

@AccessAnalytic Жыл бұрын

Glad it helped

@109zxg 8 ай бұрын

Brilliant, exact what I want to learn! Thank you

@AccessAnalytic 8 ай бұрын

Thanks for letting me know it was useful 😀

@HatemEsmat 2 ай бұрын

Awesome, Thanks a lot❤

@AccessAnalytic 2 ай бұрын

You're welcome

@tomstrack42 Жыл бұрын

Excellent!

@AccessAnalytic Жыл бұрын

Cheers

@gsbertho 10 ай бұрын

so simple and so useful !!!

@AccessAnalytic 10 ай бұрын

Glad to help

@baskis69 Жыл бұрын

Nice trick... you're a power query wizard¡¡¡ ;)

@AccessAnalytic Жыл бұрын

Thank you 😄

@entertainmentgalaxy971 9 ай бұрын

Thanks for an amazing solution. I think you can add helper column in source table to make it simple for sort order. =MONTH(DATEVALUE("1-"&[@Month]&"-1900"))

@AccessAnalytic 9 ай бұрын

Cheers

@chrism9037 Жыл бұрын

Very helpful video, thanks Wyn!

@AccessAnalytic Жыл бұрын

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

@williamarthur4801 Жыл бұрын

Like the sort trick, and thanks for mentioning about Upper before lower case sorting in previous, when I did this I had a jan in wrong order, and if you hadn't mentioned that upper sort first would have spent good while wondering why.

@AccessAnalytic Жыл бұрын

You’re welcome 😀

@Bhavik_Khatri Жыл бұрын

Nice tricks

@AccessAnalytic Жыл бұрын

Thanks Bhavik

@lorenzoladejobi8701 Жыл бұрын

Very educative!

@AccessAnalytic Жыл бұрын

Thank you

@cheikhhakim5864 Жыл бұрын

thank you sir

@AccessAnalytic Жыл бұрын

You’re welcome

@nazarkamal8831 7 ай бұрын

Awesome 👍👍👍

@AccessAnalytic 7 ай бұрын

Thanks

@juancarlosleytonfernandez9146 Жыл бұрын

Genius!!!!!

@AccessAnalytic Жыл бұрын

Thank you 🤗

@alexrosen8762 Жыл бұрын

Super smart and useful

@AccessAnalytic Жыл бұрын

Cheers Alex

@mnowako Жыл бұрын

Great one, thanks!

@AccessAnalytic Жыл бұрын

Cheers Mariusz

@hassan06ny Жыл бұрын

Thank you!

@AccessAnalytic Жыл бұрын

You’re welcome

@thebhaskarjoshi Жыл бұрын

Loved it.

@AccessAnalytic Жыл бұрын

Thanks Bhaskar

@youssefelghandour3008 Жыл бұрын

Bahebak❤

@mdwasitalinayyar Жыл бұрын

I concatenate 01-jan-2023 to make a custom column of date. The. Sort by that custom date column

@AccessAnalytic Жыл бұрын

Nice one 😀

@IMAS7512 11 ай бұрын

DataFormat.Error: Invalid cell value '#NAME?'. How to solve this error

@AccessAnalytic 11 ай бұрын

That sounds like an error in the source excel file.

@pascaljoly5752 7 ай бұрын

why did you have to group the data before adding the Index column? does it mean it wouldn't have worked if you'd added the index column without grouping the data?

@AccessAnalytic 7 ай бұрын

It helps keep the records together. So entry 1 for each person appears on same row, then entry 2 etc, otherwise it splits each entry into a unique row

@pascaljoly5752 7 ай бұрын

@@AccessAnalytic makes sense. i have managed to get the same result by creating a function that adds an index column in each sub-table that you get after grouping the data and before expanding the data. at the end of the day, it's pretty much doing the same thing but since i am new to creating functions, it made more sense to me this way to try this way. i could send you what i have done but i don't know how.

@pascaljoly5752 7 ай бұрын

the function i wrote is basic: (MyTable) => let #"Added Index" = Table.AddIndexColumn(MyTable, "Index", 1, 1, Int64.Type), #"Changed Type3" = Table.TransformColumnTypes(#"Added Index",{{"Index", type number}}) in #"Changed Type3" then all i have to do is to invoke it once i've grouped the data. seems to do the job just fine as i get the same result. am i missing something?

@AccessAnalytic 7 ай бұрын

Sounds fine to me

@kebincui Жыл бұрын

hi Wyn. I am wondering if we just add index to the intial table and then pivot without grouping. Would that also prevent error from popping up? Thanks

@AccessAnalytic Жыл бұрын

Yep, sure does but you get a single row for every entry so it looks pretty messy

@kebincui Жыл бұрын

@@AccessAnalytic Thanks Wyn for your quick clarification❤👍