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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
sv12
Helper III
Helper III

Generate data for each day

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

 

DateTerritoryConsultant
2/3/2020AS - New EnglandPaul
2/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:

DateTerritoryConsultant
2/3/2020AS - New EnglandPaul
2/3/2020AS - Central PlainsNick 
2/5/2020AS - New EnglandRyan
2/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/6

DateTerritoryConsultant
2/3/2020AS - New EnglandPaul
2/3/2020AS - Central PlainsNick 
2/4/2020AS - New EnglandPaul
2/4/2020AS - Central PlainsNick 
2/5/2020AS - New EnglandRyan
2/5/2020AS - Central PlainsNick 
2/6/2020AS - New EnglandRyan
2/6/2020AS - Central PlainsNick 

 

 

Thanks!

 

 

 

 

1 ACCEPTED SOLUTION
edhans
Super User
Super User

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

 

Basically, I did this:

  1. 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.
  2. 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.
  3. Created a new Final Table that is both of the territory child tables.

It looks like this in Power Query:

2020-03-12 20_09_19-.png

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. 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@sv12 

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.

edhans
Super User
Super User

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

 

Basically, I did this:

  1. 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.
  2. 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.
  3. Created a new Final Table that is both of the territory child tables.

It looks like this in Power Query:

2020-03-12 20_09_19-.png

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. 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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

 

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

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

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.