Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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
Need Help
  • PattemManohar's avatar
    PattemManohar
    7 years ago

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

     

    OutputInput 

2 Replies

  • PattemManohar's avatar
    PattemManohar
    Icon for Community Champion rankCommunity Champion
    7 years ago

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

     

    OutputInput 

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

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