Delete sensitive information on Power Query phase - is it still included in the .pbix file?

[Teemu]

Hi! 

I am relatively new to Power BI so forgive me if this question is dumb. I tried to look for the answer but I didint find int anywhere in the documentation so I am turning on your side.

My source data has a column that contains sensitive information which I do not want to import to Power BI. I want to be absolutely sure that the data included in the column is not available to anyone or cannot be accessed if there would be some bug or security breach. Because of this, I do not want to import the data to the Power BI. I am exporting this csv. file from other database and unfortunately the column cannot be deleted at the source database so I have to find different solution. 

I am wondering that if I choose not to import the column that contains sensitive data in the Power Query Editor window, is it still included in the .pbix file or can be accessed through Power BI Reporting Server? I am querying from folder and from Sharepoint folder, and I will share the report to Power BI Reporting Server.

Is the only foolproof solution to manually delete the column from .csv file with Power BI / Excel and DAX studio before exporting the file to Power BI?

[Anonymous]

Hi @Teemu

From security point of view, it is better to remove completly the column from your source, as you mentioned.

If you want to point the original source but not import specific columns, you can import using DAX.

A possible query can be the following:

= Csv.Document(File.Contents("Myfile.csv"),[Delimiter=",", Columns={"Column1","Column2",..."ColumnN"}
, Encoding=1252, QuoteStyle=QuoteStyle.None])

Cheers!
A

[dm-p · Accepted solution]

AFAIK the data model will only contain data that you specify as an output from Power Query. So, if you import a file with a sensitive column, and remove it during transformation, then it will not be stored in the saved workbook.

If intermediate steps were stored in the workbook then your workbook size would be much larger than it needs to be, and some data models are pretty large in their final state without having to worry about what's in there during the transformation stages.

Think of Power Query as a recipe card for your data transformation - when you're preparing a meal, there's thiings to be done at each stage, but you're only consuming the meal at the end. In Power Query, each step dictates what needs to be done to the data, but only the state of the data in the final step of that query will be persisted into your data model, as that's what you're using when you do your analysis.

Power Query will cache data on your machine for faster previewing, but this is not stored in the workbook itself.

I agree with @LivioLanzo though - if you're really that concerend about that column, then it never hurts to try and head it off at the source, so if you can get an export (or direct query) from your DB then that will ultimately be better. The CSV file existing in itself is a potential security issue if not adequately locked down.

[LivioLanzo]

Hi @Teemu,

if you can manage to get in touch with the database people then I would suggest creating a View which excludes this column and import the View instead of the table. Aren't you able to connect Power BI directly to the database instead of the CSV files?