Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
FrankPreusker
Advocate III
Advocate III

PowerQuery to understand non-blank columns per category

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:

DateActivityAttribute 1Attribute 2Attribute 3...Attribute 125
01.01.2023Avaluevaluenull null
01.01.2023Avaluenullnull null
01.01.2023Bvaluenullvalue null
01.01.2023Cvaluenullnull null
02.01.2023A
value
valuenull 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:

DateActivityColumnValue count
01.01.2023AAttribute 12
01.01.2023AAttribute 21
01.01.2023BAttribute 11
01.01.2023BAttribute 31
01.01.2023CAttribute 11
02.01.2023AAttribute 11
02.01.2023AAttribute 21

 

How can I solve this with PowerQuery? Thanks a lot in advance for your help & suggestions!

1 ACCEPTED SOLUTION
FrankPreusker
Advocate III
Advocate III

I realized this just involved a normal UnPivot operation followed by a GroupBy. I think I was just intimidated by huge number of rows/columns. Problem solved 😀

View solution in original post

1 REPLY 1
FrankPreusker
Advocate III
Advocate III

I realized this just involved a normal UnPivot operation followed by a GroupBy. I think I was just intimidated by huge number of rows/columns. Problem solved 😀

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors