Using Power query to transform a profit & loss report into pivot table readable data

[JMorrison619]

Using Power query to transform a profit & loss report into pivot table readable data

Hello,

Using Power Query, I'm importing a report that initially looks like this:



I'd like to transform it into more of a transactional format that looks like the picture below, but am unsure how to do this in an elegant fashion.

I can do it in excel fairly easily (thought it's a little cumbersome), but it would be preferable to have this done automatically anytime I update the file using power query.

Thanks for your help,

Jimmy

 

[Ken Puls]

The only question to me here is the logic that you're using to generate the Income/Expense call. Do you have a column in the source data that includes this information, or is i there a rule (Account number greater than x, ends with value greater than x or something else) that drives this classification?

Honestly, the UnPivot is super easy, just select the columns to keep (like Account), right click and choose Unpivot other columns. The only tricky part will be generating the logic to unravel the income/expense part.

 

[JMorrison619]

Hah! Genius! I didn't even know that function existed! Wow that was easy. Thanks Ken!

(FYI, to answer your question about the Income/Expense call, that was just superfluous information I included - the main issue was "unpivoting" the amounts which I now know how to do.) I literally spent all morning and half of the afternoon trying to figure this one out. Again, much thanks!

Jimmy

 

[Ken Puls]

Glad to help!