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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors