Power BI- Power Query Vs DAX - Append and Summarize data
Problem Statement- We have been provided with two tables having the same structure. We need to append the data(Union all) and then summarize it. We want data to be grouped for key values.
Tables we have
What we want
Power Query Solution
In Power Query, We will do two Operations: Append (Power Query-> Home Tab → Append Queries → Append Queries as New) and Group by (Power Query → Home Tab → Group By)
Code of the new table D3 PQ
let
Source = Table.Combine({D1, D2}),
#"Grouped Rows" = Table.Group(Source, {"Item"}, {{"Total Value", each List.Sum([Value]), type nullable number}})
in
#"Grouped Rows"
DAX Solution
In Dax, we have Union to append and we can use Summarize or Group by to group the data.
Summarize is not a fit in this case
Group by with CurrentGroup works here
The DAX code
Append DAX Group By = var _tab = Union(D1,D2)
return
GROUPBY(_tab, [Item], "Total Value", SUMX(CURRENTGROUP(),[Value]))
You can find the detailed blog here -
The file is attached.
Find all my Medium blogs here
Click Here to access all my blogs and videos in a jiffy via an exclusive visual glossary using Power BI.
Please like, share, and comment on these. Your suggestions on improvement, challenges, and new topics will help me explore more.
You Can watch my Power BI Tutorial Series on My Channel, Subscribe, Like, and share
