locked
How to refresh new data RRS feed

  • Question

  • Hello. I have a CSV file that I upload into SQL and into powerquery. I created a bunch of calculated columns on this set but realized I need to change the format a bit (delete 1 column add rows). Can I refresh this in powerquery without losing all the calculated columns and measures? 
    Wednesday, October 26, 2016 12:21 AM

Answers

  • Hi AlexMartini,

    The date table in worksheet is not changed when we create calculated column and measure. The date loaded to excel will be filtered by using Power Query, while the metadata is not changed. Based on my understanding, I try to reproduce you scenario on my local computer as follows, please review.

    1. Load the csv file using powerquery like below figure.

    2. Select a csv file, click import->load, the data will be loaded in excel, please view below screenshot.


    3. Click Power Pivot->Add to Data Model. Then I create a calculated column in data model using the formula: =Sample_Spreadsheet_10_rows__2[Column4]+20000


    4. I click and edit the Query highlighted in red in second picture, we can select any column and remove it. For example, I select and remove column3 and column4, then click “close and load”. The data in excel and data model will change automatically. Please note that the calculated column will be affected if you remove the column used by the formula(eg the calculated return error because of deleting the column3).  And it is similar to measure.


    5. Finally, using power query can filter data, while we can’t add rows in power query.  I add one row in excel, the query is not changed. Because, it just change the data from CSV file rather than  metadata.

    If this is not what you want, please post more details, thank you very much.

    Best Regards,
    Angelia
    • Marked as answer by AlexMartini Wednesday, October 26, 2016 10:49 PM
    Wednesday, October 26, 2016 10:23 AM

All replies

  • In my experience, calc columns are rarely the right solution. Read about that here http://exceleratorbi.com.au/calculated-columns-vs-measures-dax/ Probably better to use Power Pivot to unpivot your columns.

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Wednesday, October 26, 2016 9:40 AM
    Answerer
  • Hi AlexMartini,

    The date table in worksheet is not changed when we create calculated column and measure. The date loaded to excel will be filtered by using Power Query, while the metadata is not changed. Based on my understanding, I try to reproduce you scenario on my local computer as follows, please review.

    1. Load the csv file using powerquery like below figure.

    2. Select a csv file, click import->load, the data will be loaded in excel, please view below screenshot.


    3. Click Power Pivot->Add to Data Model. Then I create a calculated column in data model using the formula: =Sample_Spreadsheet_10_rows__2[Column4]+20000


    4. I click and edit the Query highlighted in red in second picture, we can select any column and remove it. For example, I select and remove column3 and column4, then click “close and load”. The data in excel and data model will change automatically. Please note that the calculated column will be affected if you remove the column used by the formula(eg the calculated return error because of deleting the column3).  And it is similar to measure.


    5. Finally, using power query can filter data, while we can’t add rows in power query.  I add one row in excel, the query is not changed. Because, it just change the data from CSV file rather than  metadata.

    If this is not what you want, please post more details, thank you very much.

    Best Regards,
    Angelia
    • Marked as answer by AlexMartini Wednesday, October 26, 2016 10:49 PM
    Wednesday, October 26, 2016 10:23 AM
  • Hi Matt, 

    Thanks for the reply (checking out the article). What do you do to avoid calculated columns when you want to see something like total sales for each customer and then bin those results. I've been using a calculated column and then a formula to bin and then I use that as row labels in my pivot table. 

    Wednesday, October 26, 2016 5:16 PM
  • If you want to bin the results and use the bins to filter the data, you must have a calc column (as you obviously know based on your good follow up question). So this may be one of those rare occasions :-). What you should do is ensure you "only" end up with the bin colum, not several interim columns. If it is easier, first create the interim column(s), then combine all the interim columns into the final single bin column. The compressed size of the final column is directly related to the cardinality of the column - small number of unique values means small fast columns.

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Wednesday, October 26, 2016 7:27 PM
    Answerer