Count frequency of text upto a hour using M

[prakhar]

I have a Table 3 with multiple columns.
Table 3
Date | Hour| Items|.....
11-2-2017| 15 | aaa,baa,caa
11-2-2017| 17 | baa,caa
11-3-2017| 18 | aaa,caa
....


I need to summarize the Hour column in a new table and in a new column of that table calculate the number of times aaa appears upto that hour for any date divided by the number of unique dates in the whole Table3.

So, table 4 would be
Hour | aaa
15 | 0.5
17 | 0.5
18 | 1

I have done it using DAX, but I need it in a M query since I will use the table 4 in another query.

[MarcelBeug · Accepted solution]

This code in an Excel Workbook will do what you are asking. You can use it in Power BI by adjustng the source.

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Hour", Int64.Type}, {"Items", type text}}),
    #"Grouped Rows1" = Table.Group(#"Changed Type", {"Date"}, {{"Count", each Table.RowCount(_), type number}, {"AllData", each _, type table}}),
    #"Expanded AllData1" = Table.ExpandTableColumn(#"Grouped Rows1", "AllData", {"Hour", "Items"}, {"Hour", "Items"}),
    #"Splitted Items" = Table.TransformColumns(#"Expanded AllData1",{{"Items", each Text.Split(_, ",")}}),
    #"Expanded Items" = Table.ExpandListColumn(#"Splitted Items", "Items"),
    #"Added Custom" = Table.AddColumn(#"Expanded Items", "aaa", (CurrentRecord) => (Table.RowCount(Table.SelectRows(#"Expanded Items", each [Date] = CurrentRecord[Date] and [Items] = "aaa" and [Hour] <= CurrentRecord[Hour]))) / CurrentRecord[Count]),
    #"Removed Duplicates" = Table.Distinct(#"Added Custom", {"Date", "Hour"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"Date", "Count", "Items"})
in
    #"Removed Columns"