Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register 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!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
87 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
60 | |
57 |