cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kvisborg Frequent Visitor
Frequent Visitor

refresh excel with altered column names

Hi all, 

 

I would like your help to figure out how to refresh a dataset in BI, when I have altered the column names?

 

In more words;

I need to extract excel sheets from a local system and then save them in my personal OneDrive each month. As the extracted columns in the excel have unclear names (I am not able to alter these in the system), I alter the column names in BI after I upload the excel files. But how do I make sure that BI recognizes the newest excel-files, and thereby able to refresh, when I alter the column names?

 

Best

//Kvisborg

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Moderator v-yuezhe-msft
Moderator

Re: refresh excel with altered column names

@Kvisborg,

"I would like to compare and sum several months"

In this case, please put all excel files to a local folder, then use Folder connector in Power BI Desktop to connect to the data source. Please make sure that the table structure in all  the original excel files are same(same columns, same column names). After that, you can rename columns in Power BI Desktop, create report in Desktop, publish report to Power BI Service.

In Power BI Service, you can set schedule refresh for the dataset after configuring gateway and adding the Folder within gateway.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Moderator v-yuezhe-msft
Moderator

Re: refresh excel with altered column names

@Kvisborg,

Do you directly connect to the Excel file in Power BI Service and then create report?

If so, you are able to rename the fields when creating visuals. And you can set onedrive refresh for the dataset in Power BI Service. This way, each month, you can upload new Excel file to OneDrive to replace existing file, new data will get updated automatically in Power BI report. Please make sure that all the excel files have same table structure(same excel file name, same columns and same column names in excel table).
Capture.PNG



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Kvisborg Frequent Visitor
Frequent Visitor

Re: refresh excel with altered column names

Thanks for your answer, @v-yuezhe-msft

 

Being entirely new to PBI, this might be a follish question so bear with me:

You ask, if I connect directly: Maybe? I Upload the excel data to PBI  manually via the Desktop solution by choosing "Get Data" (is that a direct connect?)

And if I understand you correctly, I just have to replace the exsiting excel in OneDrive with new excel each month - and before replacing I need to make sure, that the column names in the excel sheet match what I have chosen in the PBI report I have set up?

//Kvisborg

Moderator v-yuezhe-msft
Moderator

Re: refresh excel with altered column names

@Kvisborg,


Do you use web connector in Power BI Desktop? What data would you like to get in Power BI report? Data of all months or data of the latest month?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Kvisborg Frequent Visitor
Frequent Visitor

Re: refresh excel with altered column names

I choose this:

 

1) Get Data from Excel

2) And then I pick the specific excel sheet from my OneDrive

3) And then load it up to PBI desktop

 

Starting up, I only have data for the previous month, but as time goes I would like to compare and sum several months

 

Best

 

Highlighted
Moderator v-yuezhe-msft
Moderator

Re: refresh excel with altered column names

@Kvisborg,

"I would like to compare and sum several months"

In this case, please put all excel files to a local folder, then use Folder connector in Power BI Desktop to connect to the data source. Please make sure that the table structure in all  the original excel files are same(same columns, same column names). After that, you can rename columns in Power BI Desktop, create report in Desktop, publish report to Power BI Service.

In Power BI Service, you can set schedule refresh for the dataset after configuring gateway and adding the Folder within gateway.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Kvisborg Frequent Visitor
Frequent Visitor

Re: refresh excel with altered column names

THANk you so much!

also for your patience Smiley Happy

 

Good day

//Kvisborg

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 134 members 1,694 guests
Please welcome our newest community members: