Help converting DAX to M

[joshcomputer1]

I have a calculation that I need to move to the query level.  The formula works fine where it is but I need to manipulate the data into groupings later in the process.  Anyone know how to do this in M.  

All the formula does is gets the sum of the calls column by hour and date. 

Calls Per Hour = calculate(sum('table1'[Calls]), allexcept('table1', table1[date], table1[hour]))

[v-frfei-msft]

Hi @joshcomputer1,

We can use group by function in Power query to achieve your goal.

Here is the M code for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMLRQ0lEyBGJjpVgdDEFTdEEjIDaBChohqzTCJmiMLgjVHgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Hour = _t, Calls = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Hour", Int64.Type}, {"Calls", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Hour", "Date"}, {{"Count", each List.Sum([Calls]), type number}})
in
    #"Grouped Rows"

For more details, please check the pbix as attached.

Regards,

Frank

[Anonymous]

Hi, 

I assume that you want to have the calculated column instead of a summarized table, which is easier. Here is the M language which can create the same calcualted column in DAX:

let
  Source = ..... (your source)
  Table6_Table = Source{[Item="Table6",Kind="Table"]}[Data],
  #"Changed Type" = Table.TransformColumnTypes(Table6_Table,{{"Date", type date}, {"Hours", Int64.Type}, {"Calls", Int64.Type}}),

  //Add merge column: concatnate date and hour
  Table_1 = Table.AddColumn(#"Changed Type", "Merged", each Text.Combine({Text.From([Date], "en-US"), Text.From([Hours],                          "en-US")}, " "), type text),

  //Create summarized table
  #"Grouped Rows" = Table.Group(#"Changed Type", {"Date", "Hours"}, {{"Total Calls", each List.Sum([Calls]), type number}}),
  Table_2 = Table.AddColumn(#"Grouped Rows", "Merged", each Text.Combine({Text.From([Date], "en-US"), Text.From([Hours],                       "en-US")}, " "), type text),

  //Merge 2 tables, expand and delete merge column
  #"Merge 2 tables" = Table.NestedJoin(Table_1,{"Merged"},Table_2,{"Merged"},"Table 2",JoinKind.LeftOuter),
  #"Expanded Table 2" = Table.ExpandTableColumn(#"Merge 2 tables", "Table 2", {"Total Calls"}, {"Total Calls"}),
  #"Removed Columns" = Table.RemoveColumns(#"Expanded Table 2",{"Merged"})
in
  #"Removed Columns"

What this code does:

1. Create a summarized table by Date and Hr
2. Add Concanate (Date + HR) in original table and summarized table
3. Merge and expand total hr

Result:

I hope this help.

[LivioLanzo]

Hello @joshcomputer1,

you can easily do this by using the GROUP BY feature. Watch this

[joshcomputer1]

I cannot do the group by yet. AFter this column is calc'ed, I will add another column for a calculation then do a group by.  I need to do this in m.

[LivioLanzo]

In M it is possible to GROUP BY and then expand the column. If you take a look at the video link I posted you can see it

[joshcomputer1]

What I need to do is a bit more complex. The video is helpful though. 

I need to group by day and hour, but I need to divide by the total number of calls for the hour.

Hour, Calls, and ASA are all data fields, the rest are calcs. 

Total calls per hour = sum of all calls during the hour

% of total calls = the percentage of calls on that row of the total for the hour

adjusted ASA= [% of total calls] * [ASA]

[CheenuSing · Accepted solution]

Hi @joshcomputer1,

1. I created the table SampleData using Enter Data

2. Using Edit Query did the following

         a) Group by Date/Hour create sum (RawCalls) and in the aggregation used AllRows

         b) Expanded the Grouped output Table.

        c) Removed unwanted columns

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjA0MLJR0lQyA2AtGmBkqxOlgkjUG0GQ5JMDbBIWkCxKYQKSN9IELTZ4RFzgiKQY6JBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Hour = _t, Calls = _t, ASA = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Hour", Int64.Type}, {"Calls", Int64.Type}, {"ASA", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date", "Hour"}, {{"Sum", each List.Sum([Calls]), type number}, {"AllRows", each _, type table}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Date", "Hour", "Calls", "ASA"}, {"AllRows.Date", "AllRows.Hour", "AllRows.Calls", "AllRows.ASA"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded AllRows",{"AllRows.Date", "AllRows.Hour"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Sum", "TotalCalls"}, {"AllRows.Calls", "Raw Calls"}, {"AllRows.ASA", "ASA"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"ASA", Int64.Type}, {"Raw Calls", Int64.Type}, {"TotalCalls", Int64.Type}})
in
    #"Changed Type1"

3. Created the following measures

     %TotalCalls = SUMX(SampleData,DIVIDE([Raw Calls],[TotalCalls]))

      AdjustedASA = SUMX(SampleData,[%TotalCalls] *[ASA] )

4. Created a table visual sample output attached.

If this works for you please accept as solution and also give KUDOS.

Cheers

CheenuSing

[joshcomputer1]

That worked. I am not fully sure why, but it works. I was lost at the expanded rows part. 

[Anonymous]

Goto Edit Queries :

and click on Group by then goto --> Advanced and add Date and Hours in group by aggregation as  "Sum "  and total as columns.

Hope u got it