Union different group by views of same table

[JanV]

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"

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.

[v-ljerr-msft]

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.

Regards

[JanV]

Hi @v-ljerr-msft,

Thx for your input,valuable and a good step in the right direction!! 🙂 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