topic Re: Removing blanks from a defined variable in DAX/Power Query in DAX Commands and Tips

Removing blanks from a defined variable in DAX/Power Query

mgils24 — Fri, 29 Nov 2024 11:07:20 GMT

Hi all,

This is driving me crazy. In my calculated table I want to remove the rows which contain a blank. A very simple task but I can't get it working. Any help is appreciated!

I combine to columns and filter out the duplicates. This works fine but the column contains still 1 blank which I want to get rid off. The code looks very simple. As example I tried FILTER which doesn't work.

Re: Removing blanks from a defined variable in DAX/Power Query

johnt75 — Fri, 29 Nov 2024 11:16:02 GMT

You can use

Bridge SO = VAR MNSO = FILTER ( DISTINCT ( mn_rcpl[ServiceOrderNumber] ), NOT ISBLANK ( mn_rcpl[ServiceOrderNumber] ) ) VAR PUSO = FILTER ( DISTINCT ( 'Parts Usage Frequency Report - Raw Data'[Service Order] ), NOT ISBLANK ( 'Parts Usage Frequency Report - Raw Data'[Service Order] ) ) VAR CombinedSO = DISTINCT ( UNION ( MNSO, PUSO ) ) RETURN CombinedSO

Re: Removing blanks from a defined variable in DAX/Power Query

dharmendars007 — Fri, 29 Nov 2024 11:16:43 GMT

Hello mgils24 ,

The issue likely arises because FILTER expects a table and you're passing a column expression. By wrapping the column in FILTER directly within the RETURN statement, this will resolve the problem

BridgeSO =
VAR MWSO = VALUES(mn_rcp[ServiceOrderNumber])
VAR PUSO = VALUES('Parts Usage frequency report + Raw data'[Service Order])
VAR CombinedSO = UNION(MWSO, PUSO)
VAR CombinedSO2 = DISTINCT(CombinedSO)
RETURN
FILTER(CombinedSO2, NOT(ISBLANK(CombinedSO2)))

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

Re: Removing blanks from a defined variable in DAX/Power Query

mgils24 — Fri, 29 Nov 2024 12:11:04 GMT

Thanks johnt75 ! This works.

Re: Removing blanks from a defined variable in DAX/Power Query

FreemanZ — Fri, 29 Nov 2024 15:06:52 GMT

hi mgils24 ,

Could you try to replace both VALUES with DISTINCT? Will it also get what you expect?