cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
SChap Frequent Visitor
Frequent Visitor

Creating a new table in Power Query instead of a DAX espression in modeling

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?

 

DAX TABLE = SUMMARIZECOLUMNS(Forecast1[Project # Name], Forecast1%],"TO",SUM(Forecast1[Current Mth T/O at Sell]))
 
Thank you
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Creating a new table in Power Query instead of a DAX espression in modeling

@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"

 

image.pngOutputimage.pngInput 



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





View solution in original post

2 REPLIES 2
Super User
Super User

Re: Creating a new table in Power Query instead of a DAX espression in modeling

@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"

 

image.pngOutputimage.pngInput 



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





View solution in original post

SChap Frequent Visitor
Frequent Visitor

Re: Creating a new table in Power Query instead of a DAX espression in modeling

Thank you so much @PattemManohar ! Exactly what I was after, appreciate the help.

 

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,985)