Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
ila
New Member

Fill gaps between dates and extend latest value to max date

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

Capture.JPG

 

documentdatestatus
Doc 1 05/01/2023Approved
Doc 1 10/01/2023Not Approved
Doc 1 15/01/2023Approved
Doc 205/01/2023Not Approved
Doc 210/01/2023Approved
Doc 215/01/2023Approved
Doc 310/01/2023Approved
Doc 425/01/2023Approved
Doc 430/01/2023Not Approved
Doc 505/01/2023Approved
Doc 506/01/2023Approved
Doc 610/01/2023Not Approved
Doc 705/01/2023Approved
Doc 730/01/2023

Not Approved

1 ACCEPTED SOLUTION

Hi @ila ,

 

Here a solution in Power Query:

tackytechtom_0-1698601181154.png

 

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! linkedIn

#proudtobeasuperuser 

View solution in original post

4 REPLIES 4
tackytechtom
Super User
Super User

Hi @ila ,

 

How about this:

tackytechtom_0-1698585890617.png

 

 

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

NumberOfDocs =
CALCUlATE (
    COUNT ( 'Table'[document] )
)
 
Finally, I added the Status column to the Legend attribute:
tackytechtom_1-1698586044291.png

 

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! 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 :

 

documentdatestatus
Doc 1 05/01/2023Approved
Doc 1 10/01/2023Not Approved
Doc 1 15/01/2023Approved
Doc 205/01/2023Not Approved
Doc 210/01/2023Approved
Doc 215/01/2023Approved
Doc 310/01/2023Approved
Doc 417/01/2023Approved

 

This is what I would need: 

 

documentdatestatus
Doc 1 05/01/2023Approved
Doc 1 06/01/2023Approved
Doc 1 07/01/2023Approved
Doc 1 08/01/2023Approved
Doc 1 09/01/2023Approved
Doc 1 10/01/2023Not Approved
Doc 1 11/01/2023Not Approved
Doc 1 12/01/2023Not Approved
Doc 1 13/01/2023Not Approved
Doc 1 14/01/2023Not Approved
Doc 1 15/01/2023Approved
Doc 1 16/01/2023Approved
Doc 1 17/01/2023Approved
Doc 205/01/2023Not Approved
Doc 206/01/2023Not Approved
Doc 207/01/2023Not Approved
Doc 208/01/2023Not Approved
Doc 209/01/2023Not Approved
Doc 210/01/2023Approved
Doc 211/01/2023Approved
Doc 212/01/2023Approved
Doc 213/01/2023Approved
Doc 214/01/2023Approved
Doc 215/01/2023Approved
Doc 216/01/2023Approved
Doc 217/01/2023Approved
Doc 310/01/2023Approved
Doc 311/01/2023Approved
Doc 312/01/2023Approved
Doc 313/01/2023Approved
Doc 314/01/2023Approved
Doc 315/01/2023Approved
Doc 316/01/2023Approved
Doc 317/01/2023Approved
Doc 417/01/2023Approved

Hi @ila ,

 

Here a solution in Power Query:

tackytechtom_0-1698601181154.png

 

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! linkedIn

#proudtobeasuperuser 

Amazing Tom, this is perfect! I really appreciate it!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.