Forum Discussion

TheOckieMofo's avatar
TheOckieMofo
Resolver II
8 years ago

Dealing with Large Datasets

Power BI Community,   How do you deal with large datasets? Do you always utilize a live connection to a SSAS cube or import the data?   I am at my wits end. I am attempting to figure out the best...
Need Help
smpa01's avatar
smpa01
Community Champion
6 years ago

TheOckieMofo one suggestion.. query your table (select only the necesary columns) and create a dataflow. put it on schedule referesh.

 

Now for the power bi report (end user report) query that dataflow. You can atleast avoid the query evaluation time on the report refresh.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

smpa01 I have a similar issue with a large data set that I want to try to create dimenstion tables from, but it's in CSV format. Is there a way to pull select columns and distinct values of those columns from a CSV file?

  • smpa01's avatar
    smpa01
    Community Champion
    5 years ago

    AnonymousI experimented with a CSV file.

    Following is the syntax

    Csv.Document(File.Contents("C:\Users\x\Desktop\csv.csv"),[Delimiter=",", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None])

    Is there a way to pull select columns - yes, you can do that by mentioning th number of columns you want by overwriting in Columns=2. But I guess the column selection works from left to right and you can't do a SQL like SELECT to choose your columns.

     

    and distinct values of those columns from a CSV file? - since a SQL statement will not work, I doubt whether it is possible to run additional scripting.

     

    Please read to understand the parameters allowed inside CSV.Document

    https://docs.microsoft.com/en-us/powerquery-m/csv-document

     

    You can try connecting it through ODBC connector and there you can fully utilize SQL statements

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you for such a quick response!

       

      I did see the CSV parameters and for columns it does say for columns you can use "a list of column names" but when I list the columns as they are in my first row (example below) it just names them but doesn't select those columns. I tried finding if there was a way to select certain columns in CSV, but with no luck.

      Example data:

      "InsuredID","AccountName","PlanName","FirstName","MiddleInitial","LastName","IndivTripCostInsured","DepartDate","ReturnDate","CovgBeginDate ","CovgEndDate"

       

      Example code (pulls first two columns and names the respective column InsuredID and PlanName:

      = Csv.Document(File.Contents("Z:\Data\Travel\Legacy Data\Premium\AXA_PREM.csv"),[Delimiter=",", Columns={"InsuredID", "PlanName"}, Encoding=1252, QuoteStyle=QuoteStyle.None])

       

      Example code (pulls first two columns and names the respective column Column1 and Column3:

      = Csv.Document(File.Contents("Z:\Data\Travel\Legacy Data\Premium\AXA_PREM.csv"),[Delimiter=",", Columns={"Column1", "Column3"}, Encoding=1252, QuoteStyle=QuoteStyle.None])

       

      • smpa01's avatar
        smpa01
        Community Champion
        5 years ago

        Anonymous  tested and yes I can confirm the usage of list inside column parameter

        Csv.Document(File.Contents("C:\Users\X\Desktop\csv.csv"),[Delimiter=",", Columns={"Column1","Column3"}, Encoding=65001, QuoteStyle=QuoteStyle.None])