Return non-zeros and non-empty values from SAP BW Application Server Connector

[DizerX]

Hello Gurus,

I'm using SAP BW Application Server connector to extract some data from SAP BW.

Previously, when I used to query the same data from "Analysis for Office" plugin in Excel, the outputs automatically excluded non-empty, and non-zeroes, which made the extraction light.

In Power BI however, this connector seems to be pulling every single row of data, even if it doesn't have any values against it in the measures I'm trying to pull. The result is an enormous amount of data which takes too long to extract.

Now the problem, is that I don't know MDX to write a custom query. Also my understanding is that if I publish this dashboard to Power BI cloud, then MDX cannot be used in the connector anyway. I have only used the object browser that shows up after I connect to SAP BW to get the data I need, and it generated the code that I'm showing below (which works, but takes too long and generates millions of rows).

My question is: are there any modifier parameters that I can add to this code to prevent it from returning back these obsolete and empty rows of data and make the extraction faster?

Thanks!

Source = SapBusinessWarehouse.Cubes("sapkpw.companyname.com", "32", "160", [Implementation="2.0", LanguageCode="EN", EnableStructures=true]),
ZOCZZM09 = Source{[Name="ZOCZZM09"]}[Data],
#"ZOCZZM09/ZORQOC_ZOCZZM09_ZZ_001" = ZOCZZM09{[Id="ZOCZZM09/ZORQOC_ZOCZZM09_ZZ_001"]}[Data],
#"Added Items" = Cube.Transform(#"ZOCZZM09/ZORQOC_ZOCZZM09_ZZ_001",
{
{Cube.ApplyParameter, "[!V000001]", {"[ZSNPVERS].[202412]"}},
{Cube.ApplyParameter, "[!V000002]", {"[0CALWEEK].[202412]", "[0CALWEEK].[202512]"}},
{Cube.AddAndExpandDimensionColumn, "[0MATERIAL__0APO_ATTLP5]", {"[0MATERIAL__0APO_ATTLP5].[LEVEL01]"}, {"Packing Line.Packing Line Level 01"}},
{Cube.AddAndExpandDimensionColumn, "[0MATERIAL]", {"[0MATERIAL].[LEVEL01]"}, {"Material.Material Level 01"}},
{Table.AddColumn, "Material.Material Level 01.Key", each Cube.AttributeMemberProperty([Material.Material Level 01], "[20MATERIAL]")},
{Cube.AddAndExpandDimensionColumn, "[0CALWEEK]", {"[0CALWEEK].[LEVEL01]"}, {"Cal Week/Year.Cal Week/Year Level 01"}},
{Cube.AddMeasureColumn, "Closing Stock", "[50SUTVH3SGNN7NOGYSNPO53TV].[50SUTT4LZW0UIRQJ6LXYLJHYB]"}
})

[v-junyant-msft]

Hi @DizerX ,

You can try to use the Table.SelectRows function to filter out rows based on your criteria. For example, you can exclude rows where the measure values are null or zero.
Here is a sample:

FilteredRows = Table.SelectRows(#"....", each [Closing Stock] <> null and [Closing Stock] <> 0)

If you’re familiar with the structure of your data, you can apply filters to specific columns to only retrieve rows with non-empty values in those columns.
And consider using the Table.RemoveColumns function to remove unnecessary columns from your query, which can reduce the amount of data being processed and potentially speed up the extraction.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DizerX]

Hi Dino, Thank you for your reply.

Wouldn't that mean that the server side is still processing and sending the large data set into Power Query only to be filtered later on? Wouldn't this result in the same slow performance?

The idea was to get the server side to only process and send the non-zero data from its side to avoid the huge time the data needs to be processed before it's received into my Power Query.