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?
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!
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.
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.