Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Combining data from 2 tables with different date granularity

Hello, I want to calculate the weekly rotation (=rate of sales) of products sold per store.  To do that, I have two tables: - Total sales of specific products at a particular retailer. Each entr...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Thanks for your suggestions lucadelicio and Ashish_Mathur 
The model contains my company's proprietary data, so difficult to share the pbix. But here's where I am at the moment.

There's already a customized date table, including nr of week, month, year, etc.

I have merged the queries "sales" and "distribution" in a new query, with left outer. Because "sales" has the finest date granularity. Matching with "Country", "Date", "SAP" (the product ID), and "Retailer". "CU" is the quatity sold.

Now I need to copy the "Distribution.POS" value to all the rows with "null" in the future until there's a new value, matching with "Country", "SAP", and "Retailer". So I believe it's about creating a new column with a conditional formula. Can you help me with the formula?
Big thanks in advance.

lucadelicio's avatar
lucadelicio
Impactful Individual
3 years ago

In power query transform data add a conditional column.

If Column equals null then value else Column
This is the M code:
= Table.AddColumn(#"Changed Type", "Custom", each if [Column5] = null then 156 else [Column5])

Hope i help you.

Mark it as a solution if you resolve your problem.

Ciao!