Work offline in Power Query Editor

Hi
Power Query Editor. I want to work off line on the PBIX file at home in Power Query Editor. But I can’t . Power Query ask for Edit Credentials. Access to the source. All columns aren’t visible. I don’t have VPN on the PC. QUESTION: is there a way to work offline ?
Can you do some magic @Melissa ?
The source is a local SQL server only have acces to when I’am on the office.

1 Like

@amdi,

I’m pretty sure this will work:

  1. Set up a file location parameter, so you can easily switch between work and home:
  1. Use DAX Studio to export your data in each table to CSV.

  2. Set up an Excel file with each table as a separate worksheet, and import the CSV data from step #2 into the worksheet that corresponds to that table name.

  3. Point your parameter created in step #1 to the location of the Excel file, and you should be able to work locally.

I hope this is helpful.

– Brian

Hi @Amdi,

@BrianJ’s solution should let you deal with this type of scenario.

Wanted to add that if you’d need to change more query steps than just the “Source” step in each query, to implement an if-statement based on a Parameter value so it switches between the THEN and ELSE clause. For example:

let
    //THEN code block 
        LocalFile = Excel.Workbook( File.Contents( "C:\Test.xlsx" ), null, true ),
        SourceData_Table = LocalFile{[Item="SourceData",Kind="Table"]}[Data],
        ResultA = Table.TransformColumnTypes( SourceData_Table, {{"TextColumn", type text}, {"IntegerColumn", Int64.Type}, {"DecimalColumn", type number}, {"DateColumn", type date}}),
    
    //ELSE code block 
        ResultB = mySQLdbConnection,

    //Result code block 
    Source = if myParamValue = "Away" then ResultA else ResultB
in
    Source

.
Please note that only one of these THEN / ELSE blocks will be evaluated as result. You can built subsequent query steps by calling the “Source”.

For your reference:

2 Likes

Hi
@BrianJ and @Melissa
Thank you for your support.
Just now I’am trying your suggestion.
As I have more than one table there comes new issues.
Just now I have not a clear view of the difference issue.
Melissa I understand your “Table.TransformColumnTypes” , but it gives an error on this {“TextColumn”, type text}. Because it see the column as ABC123.

I will work further with this.
Thanks Amdi

Yes - sorry if this was unclear in my post above. For each table, you’ll do a separate export to CSV, and then import each of those CSVs into a separate sheet/tab of the Excel workbook. Make sure that the tab names in your workbook correspond to the table names in your model.

Just give a shout if you run into problems.

  • Brian
1 Like

Unfortunately this is just a small fictitious sample to illustrate how to structure the query, you will have to adjust it completely to suit your specific needs.
But you can use the User Interface to built the separate blocks of M code and copy/paste them into the provided sample and finally reference the last step variable name from each block in the Source step if-statement.

I hope this is helpful.

1 Like

@BrianJ and Melissa
For those who have this issue for work offline in Power Query Editor.

With BrianJ’s refering to video and Melissa’s M code and her reference to other articles I could manage to setup to work offline. It was a long journey. Therefor I will attact the PBIX file and Excel for other who’s not are professionel.

In Power Query, with the first code from Melissa in each table and if you afterwards expands with more rows the reference to the row above will work.
I did not use this part from Melissas code “Table.TransformColumnTypes( SourceData_Table, {{“TextColumn”, type text}, {“IntegerColumn”, Int64.Type}, {“DecimalColumn”, type number}, {“DateColumn”, type date}}),”. You can modify all the tables afterwards.

Attach Pbix and excel file. Then you have a complete example that works.

SQLdbConnection.pbix (683.0 KB)
EcelSqldb.xlsx (1.5 MB)

BrianJ and Melissa, thanks for all the support.

/Amdi

From DAX Studio you can export tables as CSV files.
DAX Studio

@amdi,

Glad to hear you were able to get this to work. Thanks for sharing your experience and information for others in the same situation.

  • Brian