I'm extracting and appending a significant number of json files which do result in a table with approx 5Mio rows & 127 columns. The structure looks like this:
| Date | Activity | Attribute 1 | Attribute 2 | Attribute 3 | ... | Attribute 125 |
| 01.01.2023 | A | value | value | null | | null |
| 01.01.2023 | A | value | null | null | | null |
| 01.01.2023 | B | value | null | value | | null |
| 01.01.2023 | C | value | null | null | | null |
| 02.01.2023 | A | | value | null | | null |
As you can see, depending on an Activity only certain Attribute columns do contain values while other columns are null. I'm not sure, that even for one Activity (eg 'A') always the same columns are filled. And it may change over time. As of now, I have ~250 different Activities to consider.
What do I want to achive? I want to understand, which Activities are linked to which attributes and how often the attributes do contain a value. I'm not interested in the attribute values at all, I just want to distinguish if there is a value or it contains null. The output table could look like this:
| Date | Activity | Column | Value count |
| 01.01.2023 | A | Attribute 1 | 2 |
| 01.01.2023 | A | Attribute 2 | 1 |
| 01.01.2023 | B | Attribute 1 | 1 |
| 01.01.2023 | B | Attribute 3 | 1 |
| 01.01.2023 | C | Attribute 1 | 1 |
| 02.01.2023 | A | Attribute 1 | 1 |
| 02.01.2023 | A | Attribute 2 | 1 |
How can I solve this with PowerQuery? Thanks a lot in advance for your help & suggestions!
