The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi,
I would like the solution in MQuery. I have found multiple solutions where you can index and groupby but it only shows from first row to next row not the skip level.
I want to calculate the SLA for each Store based on TaskNo. I need to know for each store from task 1 to task 3 how much time did it take to close. for example Store no 3725033 task 1 is closed on 12/15/2020 and task 3 is closed on 1/11/2023 so I want to calculate the column "Sla from taskNo 1 to TaskNo 3" by task3 closed date - task1 closed date. then I want to calculate "Sla from taskNo 4 to TaskNo 6" by task6 closed date - task4 closed date and so on shown the red columns in the table.
Store no | TaskNo | ClosedDate | Sla from taskNo 1 to TaskNo 3 | Sla from taskNo 4 to TaskNo 6 | Sla from taskNo 7 to TaskNo 9 | Total SLA from task 1 to task 9 |
3725033 | 1 | 12/15/2020 | ||||
3725033 | 2 | 1/11/2023 | ||||
3725033 | 3 | 1/11/2023 | ||||
3725033 | 4 | 1/26/2023 | ||||
3725033 | 5 | 4/25/2023 | ||||
3725033 | 6 | 5/30/2023 | ||||
3725033 | 7 | 6/2/2023 | ||||
3725033 | 8 | 6/5/2023 | ||||
3725033 | 9 | null | ||||
3725034 | 1 | 1/11/2023 | ||||
3725034 | 2 | 1/26/2023 | ||||
3725034 | 3 | 5/30/2023 | ||||
3725034 | 4 | 6/2/2023 | ||||
3725034 | 5 | null | ||||
3725034 | 6 | null | ||||
3725034 | 7 | null | ||||
3725034 | 8 | null | ||||
3725034 | 9 | null | ||||
3725035 | 1 | 5/30/2023 | ||||
3725035 | 2 | 5/30/2023 | ||||
3725035 | 3 | 6/2/2023 | ||||
3725035 | 4 | 7/2/2023 | ||||
3725035 | 5 | 7/6/2023 | ||||
3725035 | 6 | 7/16/2023 | ||||
3725035 | 7 | 7/26/2023 | ||||
3725035 | 8 | 7/30/2023 | ||||
3725035 | 9 | 8/15/2023 |
Solved! Go to Solution.
To create the first SLA (Task 1 to 3):
You can create a new column which holds a filtered list of the original table:
I have created an example here where I put the calculation for the dates in a seperate function for clarity.
Note that you can also create a function to dynamically create the other SLA columns so your code does not get so long.
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"fdI7DoMwEEXRvbhGmp/HNmtB7ACly/6DSOIMD5OCZo4E70osS7KqzmZpSrI/ysokSuJpnSLqG41EiAXQ/mH+IgtpAfSOmRS/WTo6GQPWjoVYAVtEfO28P4/ntoVz/uUfO0UA9S4ix3zcmU/5sDN/8gdTyvhcx+c2Pg8y/ZQJY/2UOUC7K/FTZr2iRyyAJaAg1oCK2AJe1s4d2/E3ry8=",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Store = _t, Task = _t, ClosedDate = _t]
),
importClean = Table.TransformColumnTypes(
Source, {{"Store", Int64.Type}, {"Task", Int64.Type}, {"ClosedDate", type date}}
),
#"Filtered rows" = Table.SelectRows(importClean, each ([Store] <> 0)),
importCleanFiltered = #"Filtered rows",
calculateDates = (inputTable) =>
let
getDates = Table.Column(inputTable, "ClosedDate"),
//calculationDebug = [max= List.Max(getDates), min=List.Min(getDates)],
calculation = Duration.Days(List.Max(getDates) - List.Min(getDates))
in
calculation,
SLA_1_to_3 = Table.AddColumn(
importCleanFiltered,
"SLA_1_to_3",
(outerrow) =>
if outerrow[Task] = 1 then
calculateDates(
Table.SelectRows(
importCleanFiltered, (row) => row[Store] = outerrow[Store] and (row[Task] = 1 or row[Task] = 3)
)
)
else
null
)
in
SLA_1_to_3
Let me know if you get stuck at any step of the code.
To create the first SLA (Task 1 to 3):
You can create a new column which holds a filtered list of the original table:
I have created an example here where I put the calculation for the dates in a seperate function for clarity.
Note that you can also create a function to dynamically create the other SLA columns so your code does not get so long.
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"fdI7DoMwEEXRvbhGmp/HNmtB7ACly/6DSOIMD5OCZo4E70osS7KqzmZpSrI/ysokSuJpnSLqG41EiAXQ/mH+IgtpAfSOmRS/WTo6GQPWjoVYAVtEfO28P4/ntoVz/uUfO0UA9S4ix3zcmU/5sDN/8gdTyvhcx+c2Pg8y/ZQJY/2UOUC7K/FTZr2iRyyAJaAg1oCK2AJe1s4d2/E3ry8=",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Store = _t, Task = _t, ClosedDate = _t]
),
importClean = Table.TransformColumnTypes(
Source, {{"Store", Int64.Type}, {"Task", Int64.Type}, {"ClosedDate", type date}}
),
#"Filtered rows" = Table.SelectRows(importClean, each ([Store] <> 0)),
importCleanFiltered = #"Filtered rows",
calculateDates = (inputTable) =>
let
getDates = Table.Column(inputTable, "ClosedDate"),
//calculationDebug = [max= List.Max(getDates), min=List.Min(getDates)],
calculation = Duration.Days(List.Max(getDates) - List.Min(getDates))
in
calculation,
SLA_1_to_3 = Table.AddColumn(
importCleanFiltered,
"SLA_1_to_3",
(outerrow) =>
if outerrow[Task] = 1 then
calculateDates(
Table.SelectRows(
importCleanFiltered, (row) => row[Store] = outerrow[Store] and (row[Task] = 1 or row[Task] = 3)
)
)
else
null
)
in
SLA_1_to_3
Let me know if you get stuck at any step of the code.
First create a new column
Number.IntegerDivide(TaskNo+2, 3)
then follow the group by method and have rest of the columns as a table.
Not inside that table use List.Average
Power BI and Power Query- Sub Category Rank and Index: https://youtu.be/tMBviW4-s4A
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
136 | |
114 | |
108 | |
66 | |
54 |
User | Count |
---|---|
227 | |
122 | |
114 | |
93 | |
92 |