Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi , I am really struggling with the following.
Below my sample data. I need to plot something similar to the chart below where at each date I have the tot amount of documents approved and not approved
document | date | status |
Doc 1 | 05/01/2023 | Approved |
Doc 1 | 10/01/2023 | Not Approved |
Doc 1 | 15/01/2023 | Approved |
Doc 2 | 05/01/2023 | Not Approved |
Doc 2 | 10/01/2023 | Approved |
Doc 2 | 15/01/2023 | Approved |
Doc 3 | 10/01/2023 | Approved |
Doc 4 | 25/01/2023 | Approved |
Doc 4 | 30/01/2023 | Not Approved |
Doc 5 | 05/01/2023 | Approved |
Doc 5 | 06/01/2023 | Approved |
Doc 6 | 10/01/2023 | Not Approved |
Doc 7 | 05/01/2023 | Approved |
Doc 7 | 30/01/2023 | Not Approved |
Solved! Go to Solution.
Hi @ila ,
Here a solution in Power Query:
Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough). Also, you need to have a date table in place for this solution to work. In our case, the table is called Date.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcslPVjBUUNJRMjDVNzDUNzIwMgZyHAsKivLLUlOUYnWQlBgaICnxyy9RwKEMr0lG6HZhNcgI3TrsKvDaZEzQDBOgoBFeM0AqjAl625Rg+IFVmOFTYUZUAJsTtMkcv4tjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [document = _t, date = _t, status = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"document", type text}, {"date", type date}, {"status", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"document"}, {{"Grouping", each addDates(_), type table [document=nullable text, date=nullable date, status=nullable text]}}), addDates = (Table as table) as table => let #"Merged Queries" = Table.NestedJoin(Table, {"date"}, Date, {"date"}, "Date.1", JoinKind.RightOuter), #"Expanded Date.1" = Table.ExpandTableColumn(#"Merged Queries", "Date.1", {"date"}, {"Date.1.date"}), #"Filled Down" = Table.FillDown(#"Expanded Date.1",{"status"}) in #"Filled Down", #"Expanded Groups" = Table.ExpandTableColumn(#"Grouped Rows", "Grouping", {"status", "Date.1.date"}, {"status", "date"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Groups", each [status] <> null and [status] <> "") in #"Filtered Rows"
Hope this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Hi @ila ,
How about this:
The diagram looks a bit different than yours. I am not sure, whether you are actually looking for a running total or whether there are some data points missing in the table you provided.
Anyway, I just added a Date dimension and used the Dates on the x-Axis of a scatter chart
Why we need a Date Table in Power BI - YouTube
Then I created a NumberOfDocs measure to be used on the y-axis
Let me know if this solves yoru issue 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Hi @tackytechtom thanks for your reply. Unfortunately this is not what I am looking for. At each specific point in time I need to know how many docs have been approved until that date (cumulative). This means that I need to fill the values gaps between dates and extend the latest value for each document until the max date value across the table. I have slightly reduced the amount opf samle data. If this is my starting point :
document | date | status |
Doc 1 | 05/01/2023 | Approved |
Doc 1 | 10/01/2023 | Not Approved |
Doc 1 | 15/01/2023 | Approved |
Doc 2 | 05/01/2023 | Not Approved |
Doc 2 | 10/01/2023 | Approved |
Doc 2 | 15/01/2023 | Approved |
Doc 3 | 10/01/2023 | Approved |
Doc 4 | 17/01/2023 | Approved |
This is what I would need:
document | date | status |
Doc 1 | 05/01/2023 | Approved |
Doc 1 | 06/01/2023 | Approved |
Doc 1 | 07/01/2023 | Approved |
Doc 1 | 08/01/2023 | Approved |
Doc 1 | 09/01/2023 | Approved |
Doc 1 | 10/01/2023 | Not Approved |
Doc 1 | 11/01/2023 | Not Approved |
Doc 1 | 12/01/2023 | Not Approved |
Doc 1 | 13/01/2023 | Not Approved |
Doc 1 | 14/01/2023 | Not Approved |
Doc 1 | 15/01/2023 | Approved |
Doc 1 | 16/01/2023 | Approved |
Doc 1 | 17/01/2023 | Approved |
Doc 2 | 05/01/2023 | Not Approved |
Doc 2 | 06/01/2023 | Not Approved |
Doc 2 | 07/01/2023 | Not Approved |
Doc 2 | 08/01/2023 | Not Approved |
Doc 2 | 09/01/2023 | Not Approved |
Doc 2 | 10/01/2023 | Approved |
Doc 2 | 11/01/2023 | Approved |
Doc 2 | 12/01/2023 | Approved |
Doc 2 | 13/01/2023 | Approved |
Doc 2 | 14/01/2023 | Approved |
Doc 2 | 15/01/2023 | Approved |
Doc 2 | 16/01/2023 | Approved |
Doc 2 | 17/01/2023 | Approved |
Doc 3 | 10/01/2023 | Approved |
Doc 3 | 11/01/2023 | Approved |
Doc 3 | 12/01/2023 | Approved |
Doc 3 | 13/01/2023 | Approved |
Doc 3 | 14/01/2023 | Approved |
Doc 3 | 15/01/2023 | Approved |
Doc 3 | 16/01/2023 | Approved |
Doc 3 | 17/01/2023 | Approved |
Doc 4 | 17/01/2023 | Approved |
Hi @ila ,
Here a solution in Power Query:
Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough). Also, you need to have a date table in place for this solution to work. In our case, the table is called Date.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcslPVjBUUNJRMjDVNzDUNzIwMgZyHAsKivLLUlOUYnWQlBgaICnxyy9RwKEMr0lG6HZhNcgI3TrsKvDaZEzQDBOgoBFeM0AqjAl625Rg+IFVmOFTYUZUAJsTtMkcv4tjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [document = _t, date = _t, status = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"document", type text}, {"date", type date}, {"status", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"document"}, {{"Grouping", each addDates(_), type table [document=nullable text, date=nullable date, status=nullable text]}}), addDates = (Table as table) as table => let #"Merged Queries" = Table.NestedJoin(Table, {"date"}, Date, {"date"}, "Date.1", JoinKind.RightOuter), #"Expanded Date.1" = Table.ExpandTableColumn(#"Merged Queries", "Date.1", {"date"}, {"Date.1.date"}), #"Filled Down" = Table.FillDown(#"Expanded Date.1",{"status"}) in #"Filled Down", #"Expanded Groups" = Table.ExpandTableColumn(#"Grouped Rows", "Grouping", {"status", "Date.1.date"}, {"status", "date"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Groups", each [status] <> null and [status] <> "") in #"Filtered Rows"
Hope this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Amazing Tom, this is perfect! I really appreciate it!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
101 | |
97 | |
88 | |
68 |
User | Count |
---|---|
169 | |
133 | |
130 | |
103 | |
95 |