Filling missing prices in dataset

Hi,

I'm trying to get the below graph with a total price of a item basket against QTY. The red line shows the price development of the price basket. In my data set not every item has been sold in every month. So, when I add all the items to the line will fluctuate a lot because the item scope with a price is not consistent. See the first table of my data example.

So I'm trying to either fill the months where there is no list price for specific items with the price from the last month before it. Or I have a list with when which list price was active and trying to make that to a month list with a value for every month. I'm not sure which solution is easiast to implement. Or perhaps another solution?

example of my data:

	Product 1	Product 2	Product 3
Jan-23	100	150	110
Feb-23	100		110
Mar-23	105	150	120
Apr-23	105
May-23	105	165
Jun-23	110	165	130

Item	Price	Start Date	End Date
Product 1	100	01/01/2023	28/02/2023
Product 1	105	01/03/2023	31/05/2023
Product 1	110	01/06/2023
Product 2	150	01/01/2023	30/04/2023
Product 2	165	01/05/2023
Product 3	110	01/01/2023	28/02/2023
Product 3	120	01/03/2023	31/05/2023
Product 3	130	01/06/2023

Thanks for the help!

Kind regards,

Pim

Need Help

v-jingzhang
3 years ago
Hi Pim123

Based on the current table structure, the first solution would be easier. After changing column types, you can select all "Product X" columns and use Fill Down to fill the null values. Notice that this is done with Power Query Editor.

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Forum Discussion

Filling missing prices in dataset

3 Replies

Related content

Filled map

Push Datasets - Datasets PostDataset 'The request could not be processed due to missing or invalid

missing data

How to fill out data with missing dates

Fill missing dates in Power Query