Forum Discussion
Removing Null values without deleting entire Row or Column
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.
4 Replies
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,
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.
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,
