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.
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.
Solved! Go to 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"
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"
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 |
---|---|
105 | |
96 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |