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

Changing data structure of Excel after import

Hi,

 

I've imported an Excel into PBI and have been happily running some beautiful reports from it. I have however not quite figured out how to amend the structure of the Excel (e.g. add a new column, delete a column, rename a column) without breaking the refresh ability of PBI. Is there a wizard of some sort to resolve conflicts?

 

Thanks,

 

Jossy

1 ACCEPTED SOLUTION

Accepted Solutions
tomdosfre Frequent Visitor
Frequent Visitor

Re: Changing data structure of Excel after import

Hi Jossy, why trying to do it outside Power BI when you can do all that you mentioned within the desktop tool.

 

You can find more info here: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-common-query-tasks/#shape-and-comb...

 

It works pretty much like Excel, so you can add columns, change column names, add calculated fields, include expressions and much more!

 

Note that even if you change the column names or add columns with calculations etc, query will continue coming smoothly.

 

Hope it helps.

View solution in original post

5 REPLIES 5
tomdosfre Frequent Visitor
Frequent Visitor

Re: Changing data structure of Excel after import

Hi Jossy, why trying to do it outside Power BI when you can do all that you mentioned within the desktop tool.

 

You can find more info here: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-common-query-tasks/#shape-and-comb...

 

It works pretty much like Excel, so you can add columns, change column names, add calculated fields, include expressions and much more!

 

Note that even if you change the column names or add columns with calculations etc, query will continue coming smoothly.

 

Hope it helps.

View solution in original post

Jossy Frequent Visitor
Frequent Visitor

Re: Changing data structure of Excel after import

Thanks loads for your reply!

 

The data set I'm using isn't finalised yet - we're adding fields and data to enrich the data set as we go. Not ideal for created a stable reporting model but needs must! Found that PBI does remember all the field names that haven't changed so it's only a minor task to update the ones that have!

 

Jossy

DavidMoss Member
Member

Re: Changing data structure of Excel after import

I too have a similar problem. I would happily use PBI but direct query mode has limitations so i thought i would use Power Query (PQ) inexcel * as a stepping stone to get the dataset into PBI desktop (PBID) and then use PBI to do enhanced data modelling. Great that works fine but when we need to update the PQ then it is necessary to do a reload into PBI losing all the enhanced modelling previously done in PBI.

This I understand hapens as it does as it says onthe tin...this is an "import" of excel file into PBID. An it imports the PQ statements into the PBI files metadata and cannot be amended.

 

Does anyone know of a solution to do a change to the PQ which can be updated in the PBID file without losing all the enhanced data modelling (measures etc...). Please don't suggest going back to the datasource and doing it there. I want to simply use PQ inexcel to import the disparate datatables into PBID and use PBI to do the datmodelling.  ??

 

 

*Using Power Query inexcel to connect live to different database sources simulates PBID DIrect Query mode.

Highlighted
DavidMoss Member
Member

Re: Changing data structure of Excel after import

I think I have just answered half of my own question.

The answer is to obviously amend the PQ imported into PBID using the Edit queries function and if necessary stepping through each "applied step" and clicking the settings icon to edit the query.

 

But this isn't exactly what i wantedd to do. As I wanted to keep the get dataset in excels PQ (connections ONLY) and the modelling in PBID. The import excel function defeats the object of excels PQ connection  only as ultimately the data set is loadedinto PBID and hence i loose the option to publish a PBI direct query (data on-premises) only report.

 

Also I liked the idea of the PQ been managed externally of PBI as i can then upload it also to SSAS for SSRS. Giving me both  on-premises and cloud reporting solutions with data aways on premises.

 

Any work around this ? 

thegeo Frequent Visitor
Frequent Visitor

Re: Changing data structure of Excel after import

Sorry to necro post. I have an answer to this question.

 

I've been working exclusively with Excel data in Power BI, and this data has also been in flux. Fields have been removed, renamed, etc. At first, I was just getting frustrated when this happened and wiping out the data and re-importing. That didn't work very well because then I would lose all my transforms. (Unfortunately, Power BI isn't good at guessing the data types in my data.) Like you and probably a lot of people drifting in here from search engines, I knew there had to be a better way.

 

One day I got curious about the Advanced Editor and looked it up. This was a game changer. Now, when I get errors updating Excel data because a field has been taken out or renamed, I right click the query in the query editor and click Advanced Editor. It's so easy from there to delete fields, rename fields, or change field types.

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)