BANANAS! Power BI dataflows has come a long way...
Вставка
- Опубліковано 22 сер 2024
- Showing some #InsaneAmazing things you can do in Power BI dataflows. This is really bananas!
Power BI dataflows data source doc: docs.microsoft...
📢 Become a member: guyinacu.be/me...
*******************
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/co...
*******************
LET'S CONNECT!
*******************
-- / guyinacube
-- / awsaxton
-- / patrickdba
-- / guyinacube
-- / guyinacube
-- guyinacube.com
**Gear**
🛠 Check out my Tools page - guyinacube.com...
#PowerBI #dataflows #GuyInACube
More Pro Tips!
Develop your whole dataflow in the PBi Desktop Power query editor, in a single "header" group (with subgroups as needed), then ctrl-c/v into a dataflow online editor. Reasons:
1. Doing any dev in the online editor is maddening, because:
a. Saving an edit to a single query triggers a schema validation, which takes anywhere from two to infinity minutes, and
b. Taking more than 10-15 min to edit a query desyncs the editor, which then stops you from saving the query at all.
2. Keeping the entire solution in a single "header" group allows you to delete and replace the whole solution by deleting/pasting that one group. Otherwise:
a. Replacing a single query in the solution brings through all of its upstream queries, appends (#) to their names, and leaves you with these duplicates to delete, which you cannot delete, because... the query you brought in depends on them.
b. Deleting multiple queries requires deleting each query/group individually, since the dataflow online editor does not do multi-select, and
c. You can't delete a query which is used as a source for other query. You need to find the tail end of you multi-query transforms, and delete from that tail end of the logic.
I really love the way, where you explain the other paths you took to find the best solution!!
Appreciate that! 👊
Thank you for answering my dataflow question in the live stream and the video!
Most welcome! Was a great question 👊
It literally pains me to think of how much time I could have saved if I had known this about a month ago. Thanks for making me more efficient - I was copy/pasting M from advanced queries......
Been there many times :)
You are awsome Patric.... I love to watch all your youtube videos for PowerBI .... Simply Superb!!!!
Dataflows are great, but the backdrop upgrade is bananas!
You like it? Patrick's family spend some time to get it to look like that. Thinking about some further tweaks - and to diversify the geekdom. It's very Marvel right now.
It's a neat setup. I don't dare to have a setup like that. My 1 year old will definitely tear it down in an instant if there is. 😂
haha yeah. It is not small child friendly. We both have older kids, so, not an issue.
Dataflow os getting better and better. Wonder when are they going to add a refresh data flow trigger on Power Automation
hmmm good question. I thought I saw something about that at MBAS. I could be mistaken though.
Meanwhile, building a custom power automate connector to run all REST APIs, including dataflow refreshes, actually only takes 5 minutes. It is just very poorly documented for the level of most dataflow users, myself included. You might want to do a video on that
@@muppetbaer - can you share a link to the reference material you found on this?
@@muppetbaer agree as i think it may open doors to more opportunities for quality data analysis too.
Hi Renan,
Here's a very good guide for it: data-marc.com/2020/01/13/manage-all-your-power-bi-refreshes-in-one-place-with-power-automate-flow/
AWESOOOMMMEEEE!!!! I was copying the m code and pasting it into blank queries. This is soooo much faster!
Right?!?! #InsaneAmazing 👊
Holy cow those were some SICK moves Patrick!
You are awesome Patrick!
Copy-paste is going to be hugely beneficial for me.
The Dataflow editor is pretty slow, so building it in Power BI Desktop instead will be much faster, then just copy-paste over.
Thanks for this Patrick!
Dataflows are almost perfect bananas... Only missing ability for multiple persons to edit/maintain the dataflow (without having to re-enter all credentials everytime), and it's still not possible to pass parameters to the dataflow from the dataset in which it is used.
This is huuge. Folks at PBI are really going bananas :O
Agreed! 👊
Wish I saw this video sooner! Would have saved me a few hours today!
Thanks
Most welcome! 👊
Nice!
Thanks for the solution.
Most welcome Amit! Thanks for watching 👊
my dad was a botanist & he'd ref to plants/fruits by their scientific names. with so many bananas already being 'discovered' by guy-in-cube, i wonder if there's enough names from the botanist book to name all of those bananas!
True story! 👊 🤣
Hey Patrick, it's just bananas. Thank u very much for your great & simple explanation.
Been watch you VIDs for years, never commented like many people who follow you, just quite in the background, speaking of background, nice going
Slight pealoff from Sackston, similar but different, Energy & Symmetry
The new Background like your VIDs got Flow . . .
Hello. Thanks for this video. This helped me a lot. Can you please let me know how to connect a Power BI data flow to Azure DevOps. I am not seeing any connector.
Is the CALENDAR function of your example a COMPUTED ENTITY?
Does it works with PRO users already? Or only premium?
Can I double like this video? This is great!
MindBlown!!!
It's the little things :)
Hello Patrick, thanks for the explanation.
could you please explain the way on how you merge and append queries within the power query of the data flows ? is it at least possible ?
When i try to do it : On-Prem execution not supported for entity '**** since it refers to another entity
thanks for your answer (if any)
Awesome post dude! Quick one. I remember some of the data flow functionality only works with a premium capacity workspace. Is that true for computed entities and functions? As in you can’t load it into a data flow or you have to disable the “enable load” on those?
dataflow is going to be the future
Does dataflows, dataprep or some other facet power bi allow one to snapshot the value of measure(s), and/or the underlying table data used to compute them,, on the 1st of every month and store the result in a power bi table for creation of monthly trend line visuals?
I literally was just trying to figure this out and decided to take a brain break to watch your channel again. Problem solved. My native queries serving as datasets can now become dataflows
Assuming you mean "now" instead of "not". If so, then BAM! 👊
@@GuyInACube thanks for the edit. Love your channel
@@GuyInACube Looks like I can't just copy and paste as I thought. Taking Teradata SQL query from Desktop to the Power Query Editor for Dataflows online. Ctrl V seems to not work. Any ideas?
hmmm may be data source dependent? Not sure without trying it. I don't have access to Teradata... We know it worked with Azure SQL Database. Maybe it gets blocked if a Gateway is needed? If you had a gateway already defined, maybe it would work then?
@@GuyInACube Yes I think it is my Gateway. It required configuration. Even after I setup the first query (the "old fashion" way with a blank query), the additional ones still wouldn't copy / paste. I still can do it via blank query which was more than I put together before. Thanks!
Awesome !!
Hi Patrick, thanks for the great tip! I, however am trying to do the opposite, i.e. copying from dataflows back to desktop. That doesn't seem to be supported yet? I know I could export the dataflow as a .json but that's not easily converted into clean m code. Cheers.
Can we add data from Multiple Data models of multiple Workspace in one data flow ?
How do you put the tables in folders? Love the videos.
In Power Query, you can great groups/folders and then move items into those groups/folders.
Guy in a Cube, thank you sir! I had seen that option earlier when I initially started Power BI and figured (at the time) it dealt with grouping data, completely forgot about it. Rock on, I’m only a few weeks in Power BI, but come with 15 years of SQL. Your videos have been perfect. They cut right to the chase with clean and concise instructions. Love them.
How can you create a relationship model in a dataflow?
So, no one is going to comment on the background? That's bananas.
Appreciate that! A few folks have commented. 👊
Hi Patrick, I have been using dataflow for over a month now. To control the dataflow to PBI, I started using Dataflow, then I copied all the power queries to dataflow to move the processing power to Dataflow. However, the power queries which otherwise were working fine in PBI Desktop is not working in Dataflow. To explain in detail, in the Dataflow edit mode, the queries run fine, then I save and close. The next step is where I have the problem, when I try to refresh the dataflow it miserably fails in a couple of secs. I couldn't figure out the reason. Would you know the issue of the problem?
What is the possibility of integration between DATAFLOW into the database (SQL for example) ???
Could you provide help in using Dataflows with files stored on OneDrive for Business? Cannot get that to work at all. Thanks
Hi Patrick, which browser are you using? It seems not to work with mine...
background is awesome. Marvel Fan
Woot! 👊 big improvement
You guys are so good if you start putting such videos for SQL people are gonna go BANANAS!
My biggest problem with Data flows is that you seem to run very fast into the boundary where you need premium capacity.
When was this gem of a feature added? Don't tell me it's there since the inception of data flows 🤔
Hey Patrick, you're Amazing. I love your accent and how you explain all the things. I would love to have all the content you share also in Spanish language. If you're looking for someone to do this Spanish translate of the content of your videos, contact me!
Hi. Im a beginner but I have ongoing power BI project in the company im working with. I hope you can answer my question. How do you open query editor in the cloud? I thought it is not available in the cloud and can only be done in desktop mode.
On any workspace click new--> dataflow--> new entities --> select the datasource and then power query appears
There is currently not a way to open the query editor in a published report in the service. You can edited the report (add change visuals, etc) but not the power query side. If you need to do power query steps in the cloud use a dataflow.
Great question Kevin! Bill pointed out how to do this for a dataflow in Power BI. As Sarah mentioned, you can't do this for a published report/dataset. Only for dataflows today.
Yeah, my feature request from way back when dataflows were introduced is live!
ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/36184816-copy-paste-m-queries-with-dependencies-from-deskto
YAY! 👊
Did anyone get the name of the blog that had list of data sources that could be copied from Desktop to Dataflows?
Forgot to include it in the description. I've added it now. It was docs.microsoft.com/power-bi/transform-model/service-dataflows-data-sources
Hei Patrick! Thanks for this video, always inspiring, thumbs up! I wished to ask this:
A colleague of mine is using PIMS. I, on the other hand I have to share with her my dataflows built in Power BI. I was thinking that a solution would be to export my dataflow to MySQL. But, is it possible? If yes, how can I achieve it. Do you have another feasible solution? Thanks in advance for the advice. Padre
Nice video @Patrickdba! I recently discovered this copy/paste to dataflows by trying it on a whim after your previous video on copy/paste! Do you have the link to the blog post you mention at 5:30(ish)? ...or is it the docs.microsoft.com....link?
When I try to do a "FuzzyNestedJoin" I get this error in Data Flows: "The name 'Table.FuzzyNestedJoin' wasn't recognized. Make sure it's spelled correctly" Here is what I am doing......... Table.FuzzyNestedJoin(#"Expanded Exclude", {"Account_Name"}, HPG_Roster, {"Name2"}, "HPG_Roster", JoinKind.LeftOuter, [IgnoreCase = true, IgnoreSpace = true, NumberOfMatches = 1])................ Any help would be amazing!
Hi, I have question regarding data flow.
We are planning to shift all of our reports to data flows and discontinue AAS cubes.
We are concerned about the refresh / performance using data flows as our enterprise data size is huge.
We are using power bi Premium capacity.
What are your views on this... ?
Any tips or resources you can suggest me to refer ... ?
6 min not 2 min
Can we create Essbase data flow in Power BI ?
Hi, need a way to access the Power BI dataflows from Excel Power Query, please help us to ask Microsoft Team for the feature. Thank!
It's not available directly today. Be sure to vote that up on ideas.powerbi.com. I can't remember if they demo'd something like that at MBAS or not. Have to believe that will come at some point.
wowwwwwwww, patrick do you know if power automation only works for premium capacity?
Power Automate has nothing to do with Power BI Premium. Those are two different services.
Power BI dataflows, though, can be used outside of Power BI Premium. There are some aspects of dataflows that requires Premium capacity.
How can you create a relationship model in a dataflow?