Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have created a summarised table using DAX from a table created by Get Data, however I am unable to use the merge table function as the DAX table doesn't appear. How can I create the same summarised table in query instead of DAX?
Solved! Go to Solution.
@SChap In Power Query, just duplicate the existing table (in this case ForeCast1) and remove all the fields apart from the ones that you are interested in. Then use "GroupBy" option to perform your aggregations. Here is the sample example with same steps performed
let Source = Csv.Document(File.Contents("C:\Users\pat22694\Documents\Pattem\06 Big Data\03 Pig\emp.csv"),[Delimiter=",", Columns=8, Encoding=65001, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"empno", Int64.Type}, {"ename", type text}, {"job", type text}, {"mgr", type text}, {"hiredate", type datetime}, {"sal", type number}, {"comm", type text}, {"deptno", Int64.Type}}), #"Added Custom Column" = Table.AddColumn(#"Changed Type", "HiredateMonth", each DateTime.ToText([hiredate], "MMM"), type text), #"Removed Columns" = Table.RemoveColumns(#"Added Custom Column",{"empno", "ename", "job", "mgr", "hiredate", "comm", "HiredateMonth"}), #"Grouped Rows" = Table.Group(#"Removed Columns", {"deptno"}, {{"Sum", each List.Sum([sal]), type number}}) in #"Grouped Rows"
Proud to be a PBI Community Champion
@SChap In Power Query, just duplicate the existing table (in this case ForeCast1) and remove all the fields apart from the ones that you are interested in. Then use "GroupBy" option to perform your aggregations. Here is the sample example with same steps performed
let Source = Csv.Document(File.Contents("C:\Users\pat22694\Documents\Pattem\06 Big Data\03 Pig\emp.csv"),[Delimiter=",", Columns=8, Encoding=65001, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"empno", Int64.Type}, {"ename", type text}, {"job", type text}, {"mgr", type text}, {"hiredate", type datetime}, {"sal", type number}, {"comm", type text}, {"deptno", Int64.Type}}), #"Added Custom Column" = Table.AddColumn(#"Changed Type", "HiredateMonth", each DateTime.ToText([hiredate], "MMM"), type text), #"Removed Columns" = Table.RemoveColumns(#"Added Custom Column",{"empno", "ename", "job", "mgr", "hiredate", "comm", "HiredateMonth"}), #"Grouped Rows" = Table.Group(#"Removed Columns", {"deptno"}, {{"Sum", each List.Sum([sal]), type number}}) in #"Grouped Rows"
Proud to be a PBI Community Champion
Thank you so much @PattemManohar ! Exactly what I was after, appreciate the help.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |