Hi, I have a simple system which stores the below data: DateTerritoryConsultant2/3/2020AS - New EnglandPaul2/3/2020AS - Central PlainsNick There will be rows added to the stable only when there is a change made in the data. For example, if I changed Consulant's name for New England territory on 2/5/2020:DateTerritoryConsultant2/3/2020AS - New EnglandPaul2/3/2020AS - Central PlainsNick 2/5/2020AS - New EnglandRyan2/5/2020AS - Central PlainsNick Since there was no change on 2/4/2020, there is no data for that date. Is there a way within Power BI that we can augment/generate data for these missing dates and for each new day? Example: from 2/3 to 2/6DateTerritoryConsultant2/3/2020AS - New EnglandPaul2/3/2020AS - Central PlainsNick 2/4/2020AS - New EnglandPaul2/4/2020AS - Central PlainsNick 2/5/2020AS - New EnglandRyan2/5/2020AS - Central PlainsNick 2/6/2020AS - New EnglandRyan2/6/2020AS - Central PlainsNick Thanks!

Take a look at what i've done in this PBIX file. Basically, I did this: Split it into two tables, one for each region. I am sure there is a way to do this without this step, but I kept getting 1 record for Feb 4 to work with, not two, and after half an hour, I punted and did this. Each child table goes through the following steps: Find the first and last date, then create a list of dates from that date range. Now I have a table of all dates. merged that table back into the main table for that region using Right Outer Join - all records from the 2nd table (my date table), and those that match from the first. Now I'll get a blank record that has Feb 4. Rearranged the columns and sorted by my date. Filled down on the Territory and Consultant. Created a new Final Table that is both of the territory child tables. It looks like this in Power Query: It only does from the first to the last date. Your example goes through a ficticious date in the future. You could accomplish this in my query by changing the "Calculated Latest" step from: = List.Max(#"Filtered Rows"[Date]) to = Date.AddDays(List.Max(#"Filtered Rows"[Date]),1) Or you could put today there with DateTime.Date(Date.TimeLocalNow()) and skip looking for the last date.

Anonymous

Not applicable

6 years ago

Solved

Generate data for each day

Hi, I have a simple system which stores the below data:

Date	Territory	Consultant
2/3/2020	AS - New England	Paul
2/3/2020	AS - Central Plains	Nick

There will be rows added to the stable only when there is a change made in the data. For example, if I changed Consulant's name for New England territory on 2/5/2020:

Date	Territory	Consultant
2/3/2020	AS - New England	Paul
2/3/2020	AS - Central Plains	Nick
2/5/2020	AS - New England	Ryan
2/5/2020	AS - Central Plains	Nick

Since there was no change on 2/4/2020, there is no data for that date.

Is there a way within Power BI that we can augment/generate data for these missing dates and for each new day?

Example: from 2/3 to 2/6

Date	Territory	Consultant
2/3/2020	AS - New England	Paul
2/3/2020	AS - Central Plains	Nick
2/4/2020	AS - New England	Paul
2/4/2020	AS - Central Plains	Nick
2/5/2020	AS - New England	Ryan
2/5/2020	AS - Central Plains	Nick
2/6/2020	AS - New England	Ryan
2/6/2020	AS - Central Plains	Nick

Thanks!

Need Help

edhans
6 years ago
Take a look at what i've done in this PBIX file.

Basically, I did this:

Split it into two tables, one for each region. I am sure there is a way to do this without this step, but I kept getting 1 record for Feb 4 to work with, not two, and after half an hour, I punted and did this.

Each child table goes through the following steps:

Find the first and last date, then create a list of dates from that date range. Now I have a table of all dates.

merged that table back into the main table for that region using Right Outer Join - all records from the 2nd table (my date table), and those that match from the first. Now I'll get a blank record that has Feb 4.

Rearranged the columns and sorted by my date.

Filled down on the Territory and Consultant.

Created a new Final Table that is both of the territory child tables.

It looks like this in Power Query:

It only does from the first to the last date. Your example goes through a ficticious date in the future. You could accomplish this in my query by changing the "Calculated Latest" step from:

= List.Max(#"Filtered Rows"[Date])

to

= Date.AddDays(List.Max(#"Filtered Rows"[Date]),1)

Or you could put today there with DateTime.Date(Date.TimeLocalNow()) and skip looking for the last date.

4 Replies

edhans

Community Champion

6 years ago

Take a look at what i've done in this PBIX file.

Basically, I did this:

Split it into two tables, one for each region. I am sure there is a way to do this without this step, but I kept getting 1 record for Feb 4 to work with, not two, and after half an hour, I punted and did this.
Each child table goes through the following steps:
1. Find the first and last date, then create a list of dates from that date range. Now I have a table of all dates.
2. merged that table back into the main table for that region using Right Outer Join - all records from the 2nd table (my date table), and those that match from the first. Now I'll get a blank record that has Feb 4.
3. Rearranged the columns and sorted by my date.
4. Filled down on the Territory and Consultant.
Created a new Final Table that is both of the territory child tables.

It looks like this in Power Query:

It only does from the first to the last date. Your example goes through a ficticious date in the future. You could accomplish this in my query by changing the "Calculated Latest" step from:

= List.Max(#"Filtered Rows"[Date])
to
= Date.AddDays(List.Max(#"Filtered Rows"[Date]),1)

Or you could put today there with DateTime.Date(Date.TimeLocalNow()) and skip looking for the last date.

edhans

Community Champion

6 years ago

I had another thought. This is not 100% tested. I feel better about my solution above, but this will do everything in one query, but you need to test for different scenarios (some regions don't exist for example.)

I cannot upload a new PBIX file right now. However, just create a new blank query, go to the Advanced Editor, remove everything, and paste this in:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtI31jcyMDJQ0lFyDFbQVfBLLVdwzUvPScxLAQoFJJbmKMXqYCpzTs0rKUrMUQjISczMKwaK+mUmZytAlZriMTGoMjEPqzJcJsYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Territory = _t, Consultant = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Territory", type text}, {"Consultant", type text}}),
    #"Calculated Earliest" = List.Min(#"Changed Type"[Date]),
    #"Calculated Latest" = List.Max(#"Changed Type"[Date]),
    #"Full list of dates" = {Number.From(#"Calculated Earliest")..Number.From(#"Calculated Latest")},
    #"Table of Dates" = Table.FromList(#"Full list of dates", Splitter.SplitByNothing(), {"Date"}, null),
    #"Changed Type1" = Table.TransformColumnTypes(#"Table of Dates",{{"Date", type date}}),
    #"Cartesian Join to original table" = Table.AddColumn(#"Changed Type1", "Cartesian Join of full table", each #"Changed Type"),
    #"Expanded Cartesian Join of full table" = Table.ExpandTableColumn(#"Cartesian Join to original table", "Cartesian Join of full table", {"Date", "Territory", "Consultant"}, {"Date.1", "Territory", "Consultant"}),
    #"Added Is Match" = Table.AddColumn(#"Expanded Cartesian Join of full table", "Is Match", each [Date] = [Date.1]),
    #"Date 1" = #"Added Is Match"[Date.1],
    #"Unique Original Dates" = List.Distinct(#"Date 1"),
    #"Added In Original List" = Table.AddColumn(#"Added Is Match", "In Original List", each List.PositionOf(#"Unique Original Dates", [Date])),
    #"Added filter to remove unneeded items" = Table.AddColumn(#"Added In Original List", "Custom", each [Is Match] = true or [In Original List] = -1),
    #"Filtered out unneeded items" = Table.SelectRows(#"Added filter to remove unneeded items", each ([Custom] = true)),
    #"Added New Consultant" = Table.AddColumn(#"Filtered out unneeded items", "New Consultant", each if [In Original List] = -1 then null else [Consultant], type text),
    #"Removed Other Columns" = Table.SelectColumns(#"Added New Consultant",{"Date", "Territory", "New Consultant"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Territory", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"New Consultant"}),
    #"Renamed Columns" = Table.RenameColumns(#"Filled Down",{{"New Consultant", "Consultant"}})
in
    #"Renamed Columns"

I was playing with the Cartesian join last night but couldn't get the logic of the overall process to work. It hit me this morning on my drive in how to make that happen.

Anonymous
Not applicable
6 years ago
edhans thanks for this! I tried to replicate it but something went off. Will have a deeper look.

I will have to create data to see if this model can tackle challenges like Adding new Territories in future

Thanks for your help!

amitchandak

Super User

6 years ago

Anonymous

Not a great way. But if you want all dates for every one

Create a new date table
Date1= calendar(min(table[Date]),max(table[Date]))

Create table with cross join
table2 = crossjon(Date1,summarize(table,table[Territory],table[Consultant]))

Appreciate your Kudos.

Forum Discussion

Generate data for each day

4 Replies

Related content

Generating Missing Data

Generate data script for warehouse

Generating link

Lakehouse Table Generate Create Table

Outbound access protection for Data Factory (Generally Available)