How to replace a column in excel that is uploaded to Sharepoint and connected to Power BI?

[Anonymous]

Hi!

What is the most efficient way to replace the content of a column in a  excel that is uploaded to Sharepoint and then connected to Power BI?

At the moment I am replacing the whole file, recreating the relationships and re adding all the measures where they were, which is very annoying and prone to errors.

The each column has +20.000 data cells.

Thank you very much for the help!

[v-yuta-msft]

Hi Alainkir,

If you change the column name or something others which may change the table structure, you should click editor query-> advanced query to change the relative column name and attribute. If you just replace some value in the column, you have no need to change the data model in power bi.

Regards,

Jimmy Tao

[Anonymous]

Hi @v-yuta-msft,

I would like to replace all the values in the column, is there a way to paste them from Excel directly into the Query editor in Power BI?

Thank you for the help!

[Chihiro]

How is file connected?

You can query folder and/or document in SharePoint from PowerBI. As long as column names and file structure remains consistent, there is no need to recreate relationships or measures. Even when file is replaced by new content (I'd suggest keeping file name consistent though).

If column name or data structure changes... then I don't think you have choice.

[Anonymous]

Hi @Chihiro, Thank you for your fast answer! The idea is to replace a target, so everything will remain the same except the content of that column. 

The Excel is first uploaded to Sharepoint and then the data is accessed by Power BI via import.

I already tried replacing it directly in Sharepoint but you get an error saying that you have already a file called like that. When I deleted the file and uploaded the new one and later refreshed the dashboard it didn't worked any more.

[Chihiro]

If you are having issue overwriting existing file in SharePoint. Try using OneDrive. It works much the same way.

I've set up Flow to save attachment from email sent from 3rd party billing system, to specific folder and have used query to refresh data.

Something like...

[Anonymous]

Hi @Chihiro,

Thank you again for your lightning fast reply. I just talked with IT and sadly we don't have available One Drive. Is there another way where I can only replace the content of the column?

Thank you for your help,

Warm regards,

Alain

[Chihiro]

Hmm, check with your IT if you have permission to overwrite existing file on SharePoint.

Other than that, I can't currently test SharePoint as I currently have issue connecting to the service. I'll check later if it's back up.

[Anonymous]

Yes, normally to my uploads I should be able to overwrite them according to IT. The message that popped up was saying something like "you can't upload this file because there is already one with this name"

[Chihiro]

Is this SharePoint Online or local deployment on a server?

If Online, you should be able to upload to Document Library and overwrite existing file by unchecking "add as a new version to existing files".

Once you hit ok, you will be asked "What would you like to do?". Choose "Replace it".

[Anonymous]

Hi @Chihiro,

I import the data from a Sharepoint Online List.

Where do I find this option to uncheck?

Warm regards,

Alain

[Chihiro]

Are you uploading to list as attachment? Or is list document library?

If as attachment, check with your IT, if version control is set and if that is forcing you to change name.

If it's document library, it's default dialog for uploading file, when there is file with same name exists within the library.

[Anonymous]

Hi @Chihiro,

Thank you very much for continuing the conversation. I upload the excel first formatting it a table and then from the "export" function available in the extra "design" tab that appears once you have it as a table. 

It is then exported as a table, is this replying to your doubt?

Warm regards,

Alain

[Chihiro]

Wait, so you import Excel as SharePoint List... then export it and load the resulting export into PowerBI?

Sorry, but I'm not sure I'm understanding your requirement.

If it is SharePoint list, why not import as is into PowerBI without first exporting it to file?

To import SharePoint list, you can follow steps in link.

https://social.technet.microsoft.com/wiki/contents/articles/40040.power-bi-two-way-to-get-data-from-...

It would help if you can detail step by step, what you are doing and what the requirement is.

[Anonymous]

Hi @Chihiro,

The first step is creating that I take is creating the excel file, then I format it as a table and export it to SharePoint. Later I go to Power BI and I import it from SharePoint.

What I want to do is replace 1 column in that excel without the need of uploading a new table and creating all the relationships again and replacing all the measures. 

Is that possible?