Power Query measure to exclude values if number in the column is below the number of dates present

anton05

New Member
Joined
Feb 21, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Hey Guys,

I am struggling to find a way to exclude items which do not have data in all the cells represented by the data range through Power Query in my pivot table.

I am therefore looking for a measure which I can include in my current measure to make sure that the following table will only include the columns with the headers: 1, 3, and 5 and thereby remove 2 and 4. The measure should be dynamic and adapt to the change of the time line slicer meaning that if the number of rows (data points) for e.g. column 2 is below the number of rows with dates, the measure should exclude this column completely.

Screenshot of the table:
2021-01-06 13_23_59-Samlet overblik - Aon.png


Furthermore, I have attached a sample workbook (Link to the download of the workbook via google drive: PowerQuery_Sample_workbook_1.xlsx)
Here, the current measure I use to this calculation can be found.


I look forward to hearing your solutions.


Best regards,

Anton
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Watch MrExcel Video

Forum statistics

Threads
1,127,722
Messages
5,626,497
Members
416,187
Latest member
L_D18

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top