Forum Discussion

hjameelq's avatar
hjameelq
Icon for Advocate IV rankAdvocate IV
9 years ago

Data Preparation on Power BI Desktop

I do have a huge dataset that has aggregated data that has to be disaggreated to be able to present it on different types of charts. The issue with that I got that I need to create a table for each question to be able to unpivot the columns so I has to unpviot the columns on the original table, it will destort the data strcuture. Therefore, is there a quick solution to this issue, or if I created a table for each question, will this effect the perfromance of loading reports or publishing the data. One more concern is the amount of tables that  I might create for each question as we do have about 500 columns. Is there a recommended way to handle this huge amount of data?

 

Thanks, -H

Need Help
Show and Tell
Tips and Tricks

8 Replies

  • dkay84_PowerBI's avatar
    dkay84_PowerBI
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago
    The compression engine in Power BI doesn't like wide data as much as it likes long data, so you may see some memory issues with a 500 column table. Other things such as cardinality will also have an impact.

    If I understand your question, I would use a reference query to your source query and remove all unnecessary columns for each table you need to make. Then do the unpivot on those new tables.
  • v-sihou-msft's avatar
    v-sihou-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    hjameelq

     

    In this scenario, since you have too many columns for each row in your dataset, I suggest you use ETL tools like SSIS to umpivot the source table, then import the destination table into Power BI. See: How To Use the Unpivot Transform in SSIS.

     

    Another option is separating the dataset into multiple datasets, each one may contains 50 columns. Then unpivot each dataset and use "append query" in Quert Editor to combine the datasets together.

     

    Also please refer to: Data Import Best Practices in Power BI

     

    Regards,

    • hjameelq's avatar
      hjameelq
      Icon for Advocate IV rankAdvocate IV
      9 years ago

      Thanks both for replying to my question, that is appreciated. 

       

      I wanted to ask few things about the limitations of using large dataset in power BI, so if it is that limited, I have a dataset with 500 columns, will it be okay according to your expereince to separate these columns into different datasets but not using power BI, but by using the excel and then I can add it as a source to my dashboard. Yes our data is a bit huge, and we tried many BI tools, and we founf power BI good as it serves our requirements. But the only concern now and that might get in our way to carry on using power and looking for another platform is the dataset size limitation.