Forum Discussion
How to add rows for missing data for different periods
Hi. I’m new to PowerBI and would need help to solve a problem I’m hitting.
The use-case is the following:
We are receiving periodically from customers some system report files. These files are created monthly, and each file contains stats of the month for each key they have (CustomerKey). We are storing these files in different customer specific folders (1 folder per customer) and loading them in PowerBI. As each customer produces and send us files at different intervals, I would like to understand the completeness of the files we have by identifying eventual files missing in-between the oldest and most recent one we received.
As an example, the following table represents what we have received from Customer-A (that has 2 Keys: CustKey-1 and CustKey-2) and from Customer-B (that has only 1 key: CustKey-3):
Path | Source.Name | CustomerKey | Data Info | YearMonthKey |
../Data/Customer-A | 2021-01.txt | CustKey-1 | Info-A | 2021/01 |
../Data/Customer-A | 2021-02.txt | CustKey-1 | Info-B | 2021/02 |
../Data/Customer-A | 2021-04.txt | CustKey-1 | Info-C | 2021/04 |
../Data/Customer-A | 2021-09.txt | CustKey-1 | Info-D | 2021/09 |
../Data/Customer-A | 2021-04.txt | CustKey-2 | Info-E | 2021/04 |
../Data/Customer-A | 2021-06.txt | CustKey-2 | Info-F | 2021/06 |
../Data/Customer-B | 2021-04.txt | CustKey-3 | Info-G | 2021/04 |
../Data/Customer-B | 2021-08.txt | CustKey-3 | Info-H | 2021/08 |
For Customer-A / CustKey-1 the oldest file is 2021/01 and the most recent is 2021/09
For Customer-A / CustKey-2 the oldest file is 2021/04 and the most recent is 2021/09
For Customer-B / CustKey-3 the oldest file is 2021/04 and the most recent is 2021/08
Here is the table I would like to have:
Path | Source.Name | CustomerKey | System | YearMonthKey |
../Data/Customer-A | 2021-01.txt | CustKey-1 | Info-A | 2021/01 |
../Data/Customer-A | 2021-02.txt | CustKey-1 | Info-B | 2021/02 |
../Data/Customer-A | Missing Source | CustKey-1 | Missing Info | 2021/03 |
../Data/Customer-A | 2021-04.txt | CustKey-1 | Info-C | 2021/04 |
../Data/Customer-A | Missing Source | CustKey-1 | Missing Info | 2021/05 |
../Data/Customer-A | Missing Source | CustKey-1 | Missing Info | 2021/06 |
../Data/Customer-A | Missing Source | CustKey-1 | Missing Info | 2021/07 |
../Data/Customer-A | Missing Source | CustKey-1 | Missing Info | 2021/08 |
../Data/Customer-A | 2021-09.txt | CustKey-1 | Info-D | 2021/09 |
../Data/Customer-A | 2021-04.txt | CustKey-2 | Info-E | 2021/04 |
../Data/Customer-A | Missing Source | CustKey-2 | Missing Info | 2021/05 |
../Data/Customer-A | 2021-06.txt | CustKey-2 | Info-F | 2021/06 |
../Data/Customer-B | 2021-04.txt | CustKey-3 | Info-G | 2021/04 |
../Data/Customer-B | Missing Source | CustKey-3 | Missing Info | 2021/05 |
../Data/Customer-B | Missing Source | CustKey-3 | Missing Info | 2021/06 |
../Data/Customer-B | Missing Source | CustKey-3 | Missing Info | 2021/07 |
../Data/Customer-B | 2021-08.txt | CustKey-3 | Info-H | 2021/08 |
Can anybody help in defining some Power Query transformation steps or Measures that could produce the table above?
Thanks
2 Replies
- amitchandak
Super User
Anonymous , You can have a table distinct period and do left merge in power query
Merge : https://radacad.com/append-vs-merge-in-power-bi-and-power-queryHi amitchandak.
Maybe I’m not getting some basic stuff here. I do not have a unique period but specific periods for each CustomerKey that differ depending on the files that are present in the folders we load.
In the above example I would have to construct a period table as such (if I understand correctly):
CustomerKey
YearMonthKey
CustKey-1
2021/01
CustKey-1
2021/02
CustKey-1
2021/03
CustKey-1
2021/04
CustKey-1
2021/05
CustKey-1
2021/06
CustKey-1
2021/07
CustKey-1
2021/08
CustKey-1
2021/09
CustKey-2
2021/04
CustKey-2
2021/05
CustKey-2
2021/06
CustKey-3
2021/04
CustKey-3
2021/05
CustKey-3
2021/06
CustKey-3
2021/07
CustKey-3
2021/08
But how can I achieve it ?
