Easily convert your Excel Power Pivot model to Power BI
Vložit
- čas přidán 11. 08. 2020
- Take your Power Pivot model in Excel and easily convert it to Power BI in just a few steps. Patrick looks at a few different ways to do this.
📢 Become a member: guyinacu.be/membership
*******************
Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.
🎓 Guy in a Cube courses: guyinacu.be/courses
*******************
LET'S CONNECT!
*******************
-- / guyinacube
-- / awsaxton
-- / patrickdba
-- / guyinacube
-- / guyinacube
-- guyinacube.com
**Gear**
🛠 Check out my Tools page - guyinacube.com/tools/
#PowerBI #Excel #PowerPivot - Věda a technologie
That was such a simple way to migrate your Power Pivot models to Power BI! Definitely going to use this in the future!
This is obviously a fairly new comment on a fairly old video, but I just wanted to say that your explanation of this operation is perfect! Clear, concise, and entertaining at the same time. Great job, Patrick!
This solution will be extremely useful in my company - we deal with too many excel files than be optimized in power BI and this speeds up the process. Thanks!
Coming into a business where there are a lot of power pivot models this feature has been a massive time saver for our Power Bi development.
Thanks, Exactly what I am looking for recently. Great tips to manage data model between excel and power BI
Very nice way to keep using the same data model/dataset. Excel does some things great and so does Power BI, of course!
This guy gotta have the best intro, always makes me smile :D
You are a Tech Guru! Thank you for your instruction!
Mind.Blown...great video and gonna give this one a try
Meu grande amigo, você acaba e salvar minha pele rsrsrsrs. Não achei nada desse conteúdo em português, mas o seu vídeo (mesmo em inglês), foi extremamente didático e consegui montar o meu relatório. Ganhou um seguidor brasileiro! Muito obrigado!!!
WOW! What the French Toast! - Thanks for Sharing
thank you! you just made my work easierrr
Wonderful!
Impressive! You Guys are killing it! I just love the way you guys make the video both interesting and informative! 🔥
Appreciate that! Always great to hear. Thanks for watching 👊
@@GuyInACube thanks
awesomeness!
WOW!!!!!!!!!!!!!!!
You blowed my mind!
Great to hear! 👊
Awesome!
any way to migrate excel having visuals and tables into Power BI ?
I love this idea, better than exporting the data. Laissez le bon temps rouler!
thanks bro, i didn't think there was anyway this was possible. Here's a like
Crazy -- But true.. Couldn't wait for it to get my hands dirty .. As usual thanks a lot Patrick
Awesome! Thanks for watching 👊
Yooo what's up?.. Awesome content as always!!
Appreciate that Manil! Thanks for watching 👊
Nuts !!!
Thanks sir you are provinding very valuable information on power bi to people like us who have very less info of power bi
Keep this up sir 😈😈😈😈
Thanks for posting this video, it helps me workaround another issue that I have been having that I'm hoping you or someone on this comment chain can help me answer.
I can connect easily to an online dataset within Power Bi and/or Excel. But, I need to transform the data but cannot figure out how to query the tables in the live connection. I've converted to DirectQuery mode, which doesn't help. Is there a way to query an online PowerBI dataset? Can it be done and still maintain the live connection?
Hi Patrick! this is great and I use this a lot. One big problem though - if I start with PowerPivot data model where I have a 'date' field... This model is imported into PowerBI, published in PBI Service, and I connect back to the published model using Excel => the 'date' field is not recognized as date, but only as text... I don't know why Excel would not recognize it as date, I need to do a workaround by creating a calculated column in Excel that converts text into date. Is there another / better way to do it, to get the date straight away from the data model that is in PBI service?? Thanks for hints :)
Patrick, i am trying to connect excel workbook using the method you have explained(2nd step you have mentioned at time 4 of your video) but i faced two errors , one at a time:
1. (Migration failed : the connection can't be used while an Mxlr Reader object is opened.)
2. (expression should be true. Parameter name: if the report is V3 we expect to have Queries CommitPointAtLastSync)
Could you please advise .
Thanks
As always great video Patrick. Do you know by any chance, when it's going to appear at the latest Get Data feature (From Power BI dataset) in Excel for us who have O365 E5 licence? Many thnx in advance, Zoran
Love
Yes that's bananas, I have another way for getting single data model, especially for on premises solutions, getting data into ssas tabular from Excel power pivot and deploy the model to the on premises AS Server with all the changes , which can also then be used connected live by Excel, without need for E5 license or latest preview features or directly connect from power bi desktop. This can further be taken to power bi workspace with a gateway, with data completely secured on premises.
Yup Excel and Pivot Tables have been able to connect to an Analysis Services source for a long while. It's potentially a little more involved though.
Is there any way to include the tables to the right when doing the import to PBI?
Hi Patrick. I have a Excel worksheet that has several tables and new tables will be added from time to time. Can Power BI query automatically create connection to new tables in Excel worksheet?
I’m a bit confused, I have a tracker I need to “transfer” to PBI, so the question is, once I have gone through all the steps you mentioned, do I need to recreate my tracker in the new Excel spreadsheet that is connected to PBI?
Hi Guy's in a Cube - how do you get that to work, would I need work to sort out some type of gateway system?
Hi Patrick, Could you please do me a favor as i want to show some data by week on week like For Jan month it will show Wk1, wk2,wk3 & wk4 also it is same repeat in next month in feb wk1, wk2 wk3 & wk4. Means every month it start with wk1
What about updated data that will trigger in Power Bi Model when we migrate data model from excel to power BI desktop .
In excel at power query editor we can select refresh duration from the data source such as csv files in share point.
Once data model migrate to power bI refresh will work or not?
Whoa. 🤯
Whoa indeed! 👊
Hey There...I have an excel document that is getting data from SQL, I have to manually refresh it daily! I need to know how to use this process to re-create that table in power bi as it is a Time heavy table and power bi isn't dealing with my times, the way I need it to. So if I can use this solution it would be perfect. But how do I then auto refresh the data ?
Thanks Patrick, very useful trick. What if the excel file is saved in SharePoint?
do we have similar feature in oracle analytical suite ?
Hey Patrick. This is great, however, how can we do this to build the data model using a SQL Server database instead of the flat excel file? Our intention is to remove the excel steps.
I'm so glad you reply to comments. I just want to confirm where do I update the data model? In Power bi isit connected to the actual excel file?
If it is that's freaken amazing but I really need to move over to CDS. It seems like the database that is the best for low code and it works much better in cds
I meant flow sorry
Great Video, once you've created your power bi data source can you still add new data to the data model from tables in Excel via power query or is it read only?
Power BI essentially makes a copy of the data model you created with Power Pivot, and stores it within its pbix file.
I have a model with adding some columns, so how power BI also adds these columns?
This is super cool Patrick. Quick question if that's doable. My one of the client has reached out to me asking if excel view could be provided on live PowerBI reports, so that on the fly they make changes/edit cell data and see immediate effect on the report. Kind of a data entry view.
If i understand that correctly, direct edits on an Excel spreadsheet would not show up immediately within a Power BI report. The spreadsheet would need to be saved and then the model refreshed before it shows up on a report.
🤯🤯🤯👍👍
Please do video on Google Analytics connection with power bi desktop
Hey,
First of all: A helpfull Video, thanks :)
It is possible that can extend the data model every month in power bi? Because we get more data into our data model in excel every month … This allows us to update our reports in excel and power bi every month without much effort.
Thanks for helping and best regards from germany ✌️
Hi, Can you do it the other way around? can you bring a powerbi data set to Power Pivot?
Is it normal that when i try to do it it tells me no table are present , but it is normal that no tables are present since I have a pivot table . do you have any suggestions thank you inadvance .
I have created a Powerpivot model and imported it into Power Bi. I created visuals in PowerBI from this model. I now to added a few new fields to the powerpivot model and I now need to imported this new poweroivot model in Powerbi with the visuals getting lost. How do I do that
Minut 6:46 red box. Get dataset to excel from service. Minut 6:46 the function is not visible. Have just updated office again. Do you have any idea why ? It's a great help for me to use it.
You have to opt into the insider build current as it's a preview item in Excel. It's possible your tenant admin has blocked that. We had to add a registry change to get the insider option. Check out this blog - www.excel-exercise.com/how-to-become-office-insider/
Great, any idea if you can do it the other way - that is take a model from PowerBI and “export it” to Excel - not live connect, but offline copy?
There is not a direct way to do that. You could potentially re-import all the data into Power Pivot, but you would lose a lot in translation. Not recommended.
This is banana!!!!!!! :) WOW.
😂
YES!
Geaux Tigers!
So does PBI do an image load of the DataModel into the msdsrv service? Neat! Does it work with PRO subscription via the API? If so then I have an idea 🤓
There is a model bim file within the Excel file. So, yes. It effectively loads the model. Unfortunately, there is not a way to do this from an API perspective.
I try to make this work since i have a big model relying on Power Querry and Power Pivot to convert to Power BI. However after importing it never is possible to refresh the data because the Power Querry Steps run on errors. It looks a bit like the source is allready transformed and then the transformation steps are again copied and done in Power BI. Have you or has anyone transoformed a Excel Datamodel relyin on Power Querry Transformations to a working and refreshable Power BI Datamodel?
Nice way to convert your work to PowerBI - only thing missing that it seems to create import mode data connections which are not able to switch back to DirectQuery.
That's correct. Excel doesn't support DirectQuery and this would have to be defined at the time of creation of the model. If you want DirectQuery, you don't want to start with Excel.
@@GuyInACube Hm probably this is a functionality which will come in the future - shouldn't be to hard to implement as this is already available if you e.g. copy from Excel Power-Query to PowerBI you will be asked if this should be an import or direct query data source...
That's really very helpful.
A quick question, if I publish my power pivot model using power bi desktop and using that.
How new data will be added to that model?
Do I need to refresh old power pivot model with new data and publish again?
Once you convert your Excel/Power Pivot model to Power BI Desktop, you are in Power BI Desktop now. It will not pick up future changes to the Excel workbook. You would need to handle the refresh from the Power BI dataset perspective.
@@GuyInACube Thank you for the answer experts.
@@GuyInACube But is there a way to refresh it or do you have to basically do a new "import excel data model", breaking everything you did in BI, making this import data model idea impractical?
You imported the excel data model to Power BI. Next week the excel file has changes, and the BI data is stale. Is there a method to refresh the excel data model that doesn't destroy any work you did in BI? (calculated fields, visuals)
Does this apply to Google Sheets as well?
No this is only available for Excel workbooks.
Me: Gets Excel spreadsheet from Finance team...
Finance: We need PowerBI to connect to that, since that is where our Model is
Me: But PowerBI will replace it, you don't need Excel anymore.
Finance: okay, but you need to make PowerBI do what Excel does....
Me: ...
*this video exists*
Me: is this what Mana from Heaven feels like?
I tried to import the model from Excel to Power BI but I'm getting the following error "Failed to import Model from Excel as the upgrade to Tabular failed". I also had black screen flashed with SQLDUMPER.EXE file activated and this shut down the power bi for some reason. I posted this to Power BI community forum. So far no reasonable answers.
A dream coming true. Now the last piece of the puzzle would be the ability to sync a data model in the Power BI service with an on-premises Tabular Model in SQL Server AS.
hmmm what's the scenario where you would want to sync models between the two locations? That's not something we've heard before as a feature ask.
Guy in a Cube I don’t think it would be widely sought after. The reason I thought about is that we have a limited number of users on the Power BI service, but in the same time we have Power BI Report Server on-premises which allows us to build reports (paginated or otherwise) for the remaining users who don’t have a pro license. I just want to build a data model once and use it in both environment 😊
It's not working for me. Here my excel is connected to the SQL server. Kindly help me with this.
d o p e
i need ur help in a work can u help me in private?