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
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!
Solved! Go to Solution.
Take a look at what i've done in this PBIX file.
Basically, I did this:
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:
Or you could put today there with DateTime.Date(Date.TimeLocalNow()) and skip looking for the last date.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingNot 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.
Take a look at what i've done in this PBIX file.
Basically, I did this:
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:
Or you could put today there with DateTime.Date(Date.TimeLocalNow()) and skip looking for the last date.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI 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.
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!
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.
User | Count |
---|---|
158 | |
109 | |
96 | |
83 | |
75 |
User | Count |
---|---|
154 | |
137 | |
131 | |
81 | |
62 |