Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jossy
Regular 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
tomdosfre
Advocate I
Advocate I

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
DavidMoss
Advocate V
Advocate V

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.

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 ? 

tomdosfre
Advocate I
Advocate I

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.

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

thegeo
Frequent Visitor

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors