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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors