Thank you! This helped so much. For those who still get errors after doing this, check these: 1. If two or more continuos rows in the column you want to pivot have the same name, for example "Tax" and "Tax", when you pivot the column, you will get an error. I had to go to the source and add a space after one of the words to make them different. Note that it doesn't matter if the rows are repeated in different instances, like this "Tax, Fee, Tax" it only happens when one is below or over the other. This will also happen if you have two or more rows in the column you want to pivot have blanks or nulls (Power Query sees them as repeated values). 2. If you have nulls and you get an error saying that null can't be converted to number or something similar, then replace nulls with blanks (if that doesn't mess with your data in any way).
Nice one Doug! Actually, in your example, you can skip adding the Index column and do the conditional column against the Amount column, pulling the Invoice number since it is unique. Then, you can fill down and complete as demonstrated. It just saves a step or two if you have access to something that is already unique in one of the existing columns, then no index is needed. Great example! Thanks for sharing it :)) Thumbs up!!
How do you pivot a column correctly when there are repeated column headers in it? For example, when you added fee, what if I had two rows with the title "fee"? Thanks.
having multiple rows as a column header will not work. First row should be the header row so if you can removed the additional header row or incorporate the text into the first row that would be optimal.
I am totally lost. When I try this I get an error 'DataFormat.Error: We couldn't convert to Number' but my column is already formatted as a number. What is it trying to convert? So frustrating!
Thank you! This helped so much. For those who still get errors after doing this, check these:
1. If two or more continuos rows in the column you want to pivot have the same name, for example "Tax" and "Tax", when you pivot the column, you will get an error. I had to go to the source and add a space after one of the words to make them different. Note that it doesn't matter if the rows are repeated in different instances, like this "Tax, Fee, Tax" it only happens when one is below or over the other. This will also happen if you have two or more rows in the column you want to pivot have blanks or nulls (Power Query sees them as repeated values).
2. If you have nulls and you get an error saying that null can't be converted to number or something similar, then replace nulls with blanks (if that doesn't mess with your data in any way).
Nice one Doug! Actually, in your example, you can skip adding the Index column and do the conditional column against the Amount column, pulling the Invoice number since it is unique. Then, you can fill down and complete as demonstrated. It just saves a step or two if you have access to something that is already unique in one of the existing columns, then no index is needed. Great example! Thanks for sharing it :)) Thumbs up!!
Hi Wayne good point! Thanks for adding knowledge to the thread!
Wow! Awesome video! I never knew you could do that... And it is so simple! 🙂 Thank you.
Glad you liked it!
Thank you! Very useful!
Thanks, glad u thought it useful!
very informative and useful. Thanks
You're welcome!
And still, I can not keep two or more columns and pivot all the remaining columns. It appears pivoting works for a single column only.
How do you pivot a column correctly when there are repeated column headers in it? For example, when you added fee, what if I had two rows with the title "fee"? Thanks.
having multiple rows as a column header will not work. First row should be the header row so if you can removed the additional header row or incorporate the text into the first row that would be optimal.
How/where can i learn Power Query please?
Amazing working
Thanks for the comment!
I am totally lost. When I try this I get an error 'DataFormat.Error: We couldn't convert to Number' but my column is already formatted as a number. What is it trying to convert? So frustrating!
delete "Changed type" step
Nice Trick.
Thanks!
Nice! Sir
Thanks!
Awesome
Glad you liked!