Removing Null values without deleting entire Row or Column

[Haleem]

I have a huge dataset, which really impact performance on the user level especially cross filtering and multiple visual interactions. I would like to clean up and refine my datasets. Actually due to huge size of data I cannot pull it in Power BI from SAP (due to limitation of  memory on internal tables) in one go, so I have made a work around and have pulled data in pieces into 9 tables and then I merge these table into one. Attached you can see the sample of the tables, but I got huge data and 40% of the number of rows are based on null values. https://1drv.ms/f/s!AtMKO3vGEsG5gSa9J8uF0Y0T6Nj4

My questions are

1. How well I should combine these 9 tables (3 sample tables are given ) in order to reduce the number of rows by REMOVING null Values without deleting the entire row or entire column as you can see in the attached sample that if I just filter nulls on the column level, i will loose data on other column and rows. In the excel file you can see if i just append table i get many null values..

2. What could be the best practice

   2.1. Merge/Append the tables?

   2.2. don't merge or append all tables into one, instead create relationship among the individual tables? in this way we will not have the null cells because I can easily filter out the null values as shown in below picture as there is only one column in each table.

[v-jiascu-msft]

Hi @Haleem ,

Did you solve it? Could you please mark the proper answers as solutions?

Best Regards,

[v-jiascu-msft]

Hi @Haleem,

As we can see from the sample, they seem different data. I would suggest you create several dimensional tables and establish relationships. Finally, you can get data from proper source tables.

Best Regards,

[Haleem]

Thanks for your reply. 

What about if I append the tables, and then unpivot the Columns, so now i have all the columns as rows and got one column "Value". So next step will be to define "Measures" for each of the column. So in the below example, I will have one measure for Actuals, one for Estimate and one for budget.... In this way, i won't have any null values but will have more measures than columns.

My question is, whether creating many measures will impact ultimate user run time experience? I believe measures always execute on report level and impact user experience. I do not risk a slow performance on cross filtering etc. 

[v-jiascu-msft]

Hi @Haleem ,

I think that won't be a problem. Even the columns will be aggregated in the reports. For example, total Budgets of every Account. What's more, your measures here are quite simple. You can give it a try anyway.

Best Regards,