cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JanV
Advisor

Union different group by views of same table

All,

 

For the Sankey custom visual one needs a single table with following format "Origin", "Destination", "Value".

I want to create visibility on the financial relevance of a company's hierarchy (Business Unit->Division -> Area -> Branch) by showing the flow between the different hierarchy components from Business Unit -> Branch. Currently the data is in transactional format genre "1/01/2017", "€400", "Europe", "Sales", "Paris". What I currently do is creating a group by statement of the value per combination and afterwards appending all these queries in order to have a single table for the hierarchy with the Origin, Destination, Value format.

 

let
Source = AccountingSankey1,
#"Appended Query" = Table.Combine({Source, AccountingSankey2, AccountingSankey3, AccountingSankey4})
in
#"Appended Query"

 

image.pngimage.png

However this gives me a lot of queries (visual complexity) which i wouldn't need if I was able to create a single query that could capture the full complexity of the problem.

 

Any help greatly appreciated.

2 REPLIES 2
v-ljerr-msft Super Contributor
Super Contributor

Re: Union different group by views of same table

Hi @JanV,

"Enable Load" means query results are available for report builder. Otherwise you may use it in your other queries (for example to merge data), but it is not shown in the report builder.

According to your description above, you can uncheck the "Enable Load" option(by right click the query) for the 'AccountingSankey1', 'AccountingSankey2', 'AccountingSankey3', 'AccountingSankey4' query, then these queries can still be used for Append Query, but they won't be shown in the report view.

 

enableload.PNG

 

Regards

JanV
Advisor

Re: Union different group by views of same table

Hi @v-ljerr-msft,

 

Thx for your input,valuable and a good step in the right direction!! Smiley Happy Would still be interested to know if the "clutter" in the query view could be further reduced as well by not having to define all these views in seperate statements by combining them in a single m query.

 

Regards

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 55 members 1,147 guests
Please welcome our newest community members: