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 q...
Need Help
Show and Tell
Tips and Tricks
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.

     

     

     

    • v-sihou-msft's avatar
      v-sihou-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

      hjameelq

       

      In Power BI, we have the 1 GB limitation per dataset. So if your dataset will exceed this size, please separate into multiple datasets.

       

      Regards,

      • dkay84_PowerBI's avatar
        dkay84_PowerBI
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago
        1gb AFTER compression.

        Another option might be using Direct Query, if your source data is a supported option. Then you can pull in only the data you actually use instead of importing the whole data set
    • LaurentCouartou's avatar
      LaurentCouartou
      Icon for Solution Supplier rankSolution Supplier
      9 years ago

      I understand you have survey data, presented in rows (UserId, Question, Response) and you want to be able to answer questions like "how many customers answered A to question 1 and answered B to to question  2?"

       

      Creating new columns is the way to go, if you want to use an un arbitrary number of filters.

       

      Alternatively, Marco Russo and Alberto Ferrari have proposed a pattern that only allows the user to apply a predefined number of filters:

      http://www.daxpatterns.com/survey/

       

      This drawback comes with the advantage that no unpivoting is required, resulting in less memory being required (due to fewer columns being present in the data model).

       

      See if it works for you.

      • dkay84_PowerBI's avatar
        dkay84_PowerBI
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago
        How does this turn into 500 columns?