Show data for unavailable data date similar to available data date

[Surekha_PM]

Hello Everyone,

I have one table which is showing data on a weekly basis and want to reflect the data on a daily basis. Below is a small example;

     Date                       Details
14-Jun-2024          Product from XYZ
17-Jun-2024          Product from ABX
20-Jun-2024          Product from SWD
21-Jun-2024          Product from AQQ
25-Jun-2024          Product from FRE

Above mention data is the actual data we are getting from database. Now below is how I want to show the data in the dashboard;

     Date                       Details
14-Jun-2024          Product from XYZ
15-Jun-2024          Product from XYZ
16-Jun-2024          Product from XYZ
17-Jun-2024          Product from ABX
18-Jun-2024          Product from ABX
19-Jun-2024          Product from ABX
20-Jun-2024          Product from SWD
21-Jun-2024          Product from AQQ
22-Jun-2024          Product from AQQ
23-Jun-2024          Product from AQQ
24-Jun-2024          Product from AQQ
25-Jun-2024          Product from FRE
26-Jun-2024          Product from FRE


Since 26 June is yesterday that is why the table is updated accordingly. Could please help me if you have tried this kind of scenario before.

Regards,
Surekha

[Anonymous · Accepted solution]

Hi @Surekha_PM 

Here is my solution. Most of the steps can be done with the GUI.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTR9SrN0zUyMDJR0lEKKMpPKU0uUUgrys9ViIiMUorVASoxx6nE0SkCrMTIAKeS4HAXiBJDuBIFDGMCAyFqTHEa4xbkqhQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Details = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Details", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"}, #"Added Index1", {"Index.1"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Date"}, {"Added Index1.Date"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Added Index1",null,Date.From(DateTime.LocalNow()),Replacer.ReplaceValue,{"Added Index1.Date"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Added Index1.Date", "endDate"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Index", "Index.1"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "NewDate", each List.Dates([Date],Duration.Days([endDate]-[Date]),#duration(1,0,0,0))),
    #"Expanded NewDate" = Table.ExpandListColumn(#"Added Custom", "NewDate"),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded NewDate",{"NewDate", "Details"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Other Columns",{{"NewDate", "Date"}})
in
    #"Renamed Columns1"

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[aduguid]

In Power BI, go to Home > Transform data to open the Power Query Editor.

Add a custom column to calculate the end date:

Add a custom column to determine the end date for each entry. The end date will be the start date of the next entry minus one day, or the current date for the last entry.

Go to Add Column > Custom Column and use the following formula:

=Table.AddColumn(#"PreviousStepName", "End Date", each if [Date] = List.Max(#"PreviousStepName"[Date]) then DateTime.LocalNow() else List.Min(List.Select(#"PreviousStepName"[Date], each _ > [Date])) - #duration(1,0,0))

Generate daily dates:

Create a new table that contains all dates between the start and end dates for each entry.Use the following formula to generate a list of dates:

= Table.AddColumn(#"PreviousStepName", "Daily Dates", each List.Dates([Date], Duration.Days([End Date] - [Date]) + 1, #duration(1,0,0,0)))

Expand the list of daily dates:

Expand the Daily Dates column to create a new row for each date in the list.Click the expand icon next to the Daily Dates column and select Expand to New Rows.

Remove unnecessary columns and rename columns if needed:

Remove the original Date and End Date columns, leaving the Daily Dates and Details columns.

Rename Daily Dates to Date.