Power BI desktop, power query transposing part of a table

Used for anything related to the new Power BI Desktop and Service tools
datamonkey
Posts: 1
Joined: Thu Oct 17, 2019 8:56 pm

Power BI desktop, power query transposing part of a table

Postby datamonkey » Thu Oct 17, 2019 9:07 pm

Hello,

I am using Power BI Desktop to extract data from loads of Excel files and tabs, and I am having an issue transposing part of a table. Here is an illustrative example of what I'm after:

Data before:

Department Year Country Column_Name Article_1 Article_2 Article_3 Article_4
D1 2018 US Online_Sales 0 50 75 11
D1 2018 US Offline_Sales 0 20 32 45
D1 2018 US Total_Sales 0 70 107 56
D1 2018 US Number_of_articles_sold 0 10 10 10
D1 2018 UK Online_Sales 874 54 0 5
D1 2018 UK Offline_Sales 48 24 0 52
D1 2018 UK Total_Sales 922 78 0 57
D1 2018 UK Number_of_articles_sold 10 10 0 10
D2 2018 DK Online_Sales 51 55 0 51
D2 2018 DK Offline_Sales 21 988 0 17
D2 2018 DK Total_Sales 72 1043 0 68
D2 2018 DK Number_of_articles_sold 10 10 0 10
D2 2018 GE Online_Sales 51 55 0 51
D2 2018 GE Offline_Sales 21 988 0 17
D2 2018 GE Total_Sales 72 1043 0 68
D2 2018 GE Number_of_articles_sold 10 10 0 10
[attachment=0]Example Power BI Q.xlsx[/attachment]



Data After:

Department Year Country Column_Name Online_Sales Offline_Sales Total_Sales Number_of_articles_sold
D1 2018 US Article_1 0 0 0 0
D1 2018 US Article_2 50 20 70 10
D1 2018 US Article_3 75 32 107 10
D1 2018 US Article_4 11 45 56 10
D1 2018 UK Article_1 874 48 922 10
D1 2018 UK Article_2 54 24 78 10
D1 2018 UK Article_3 0 0 0 0
D1 2018 UK Article_4 5 52 57 10
D2 2018 DK Article_1 51 21 72 10
D2 2018 DK Article_2 55 988 1043 10
D2 2018 DK Article_3 0 0 0 0
D2 2018 DK Article_4 51 17 68 10
D2 2018 GE Article_1 51 21 72 10
D2 2018 GE Article_2 55 988 1043 10
D2 2018 GE Article_3 0 0 0 0
D2 2018 GE Article_4 51 17 68 10



Thank you very much in advance!

Laure
Attachments
Example Power BI Q.xlsx
tables above in excel
(14.5 KiB) Downloaded 10 times

stasinek
Posts: 3
Joined: Thu Feb 07, 2019 5:34 pm

Re: Power BI desktop, power query transposing part of a table

Postby stasinek » Sat Nov 16, 2019 10:24 pm

Hello,
See for such a solution.
best regards
Attachments
Example Power BI Q.xlsx
(25.94 KiB) Downloaded 7 times

RamanaV
Posts: 39
Joined: Thu Oct 19, 2017 12:57 pm

Re: Power BI desktop, power query transposing part of a table

Postby RamanaV » Sun Nov 17, 2019 11:00 am

stasinek,

A good solution.
Thanks for that.

Laure,

You said you are looking for a solution in Power BI Desktop
Power Query commands are the same in both Excel and Power BI Desktop

For your quick reference, here is the solution in Power BI Desktop

Ramana
Attachments
Solution_before_after.pbix
(38.06 KiB) Downloaded 7 times
Ramana Varanasi
Training and Office Manager
Excelerator BI Pty. Ltd.


Return to “Power BI Desktop/Service”

Who is online

Users browsing this forum: No registered users and 1 guest

cron