Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Solved! Go to Solution.
OK, I've done it.
Here's the M script:
let Source = <A reference to your table here>, #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Number", Int64.Type}, {"Name", type text}}), #"Buffered Table" = Table.Buffer(#"Changed Type"), #"Add Day 28 Days Back" = Table.AddColumn(#"Changed Type", "Date28DaysBack", each [Date] - #duration(27, 0, 0, 0)), #"Add Count of Name (28 Days Back)" = Table.AddColumn( #"Add Day 28 Days Back", "Count Of Name (28 Days Back)", (r) => let A = Table.SelectRows(#"Buffered Table", each [Name] = r[Name] and r[Date28DaysBack] <= [Date] and [Date] <= r[Date]), CountOfRows = Table.RowCount(A) in CountOfRows ), #"Removed Auxiliary Column (28)" = Table.RemoveColumns(#"Add Count of Name (28 Days Back)",{"Date28DaysBack"}), #"Add Day 30 Days Back" = Table.AddColumn(#"Removed Auxiliary Column (28)", "Date30DaysBack", each [Date] - #duration(29, 0, 0, 0)), #"Add Count of Combination (30 Days Back)" = Table.AddColumn( #"Add Day 30 Days Back", "Count Of Combination (30 Days Back)", (r) => let A = Table.SelectRows(#"Buffered Table", each [Number] = r[Number] and [Name] = r[Name] and r[Date30DaysBack] <= [Date] and [Date] <= r[Date]), CountOfRows = Table.RowCount(A) in CountOfRows ), #"Removed Auxiliary Column (30)" = Table.RemoveColumns(#"Add Count of Combination (30 Days Back)",{"Date30DaysBack"}) in #"Removed Auxiliary Column (30)"
And here's what my Source table looks like:
Best
Darek
OK, I've done it.
Here's the M script:
let Source = <A reference to your table here>, #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Number", Int64.Type}, {"Name", type text}}), #"Buffered Table" = Table.Buffer(#"Changed Type"), #"Add Day 28 Days Back" = Table.AddColumn(#"Changed Type", "Date28DaysBack", each [Date] - #duration(27, 0, 0, 0)), #"Add Count of Name (28 Days Back)" = Table.AddColumn( #"Add Day 28 Days Back", "Count Of Name (28 Days Back)", (r) => let A = Table.SelectRows(#"Buffered Table", each [Name] = r[Name] and r[Date28DaysBack] <= [Date] and [Date] <= r[Date]), CountOfRows = Table.RowCount(A) in CountOfRows ), #"Removed Auxiliary Column (28)" = Table.RemoveColumns(#"Add Count of Name (28 Days Back)",{"Date28DaysBack"}), #"Add Day 30 Days Back" = Table.AddColumn(#"Removed Auxiliary Column (28)", "Date30DaysBack", each [Date] - #duration(29, 0, 0, 0)), #"Add Count of Combination (30 Days Back)" = Table.AddColumn( #"Add Day 30 Days Back", "Count Of Combination (30 Days Back)", (r) => let A = Table.SelectRows(#"Buffered Table", each [Number] = r[Number] and [Name] = r[Name] and r[Date30DaysBack] <= [Date] and [Date] <= r[Date]), CountOfRows = Table.RowCount(A) in CountOfRows ), #"Removed Auxiliary Column (30)" = Table.RemoveColumns(#"Add Count of Combination (30 Days Back)",{"Date30DaysBack"}) in #"Removed Auxiliary Column (30)"
And here's what my Source table looks like:
Best
Darek
User | Count |
---|---|
40 | |
26 | |
22 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |