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
