Best tips when working with Power BI dataflows!
Vložit
- čas přidán 5. 07. 2024
- I have been creating quite many dataflows lately and in today's video, I am going to share my best tips on how to set them up and avoid common issues.
Chapters:
00:00 Intro
00:30 Is it worth to work with dataflows?
01:30 Managing gateways ...or not
02:30 Configure your own datalake
03:30 The Power Query Online experience
10:00 Computed entities
11:00 Save and refresh dataflows
14:00 Incremental refresh
14:30 Power BI dataflow connectors
Done!
Here you can download all the pbix files: curbal.com/donwload-center
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
Our PLAYLISTS:
- Join our DAX Fridays! Series: goo.gl/FtUWUX
- Power BI dashboards for beginners: goo.gl/9YzyDP
- Power BI Tips & Tricks: goo.gl/H6kUbP
- Power Bi and Google Analytics: goo.gl/ZNsY8l
☼☼☼☼☼☼☼☼☼☼
POWER BI COURSES:
Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
curbal.com/courses-overview
☼☼☼☼☼☼☼☼☼☼
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/contact
▼▼▼▼▼▼▼▼▼▼
If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:
curbal.com/product/sponsor-me
Many thanks in advance!
▲▲▲▲▲▲▲▲▲▲
************
************
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► goo.gl/bME2sB
#CURBAL #SUBSCRIBE
This is so spot on, Ruth! You nailed all my headaches and heartaches in one enjoyable video. Not only did you capture the technical issues, your face also captured the precise set of emotions I feel when faced with these challenges.
Thanks, I did it in am attempt to help others if they faced the same issues. I see now I was not alone!
Thank you for this clear call for improvement!
Ruth, thank you so much. I struggled with same issues and hope it will help others not to waste time and keep sanity. Should've watch this long ago. Your tips are golden.
Glad it helped, it drove me nuts too!!
That's where the privacy settings are !!! Thanks you so much for that video. I've encountered many of the issues you are referring to in the past.
Took me forever to find too!
Thanks for the insights. Conceptually, dataflows is awesome, but I can't wait until the bugs are worked out of it. Keep the videos coming.
Yes, me too!!
Hi Ruth! Thanks for the video. I have been suffering with many of the issues you mentioned here. Now K know I have to be aware of your tips and... Patience!!! Curbal mola!
Yes, patience will definately help!
I'm totally with you on the slowness. I did the same when I just work in desktop and copy to dataflow.
While I'm watching this my dataflow PQ keeps and keeps validating. :D
Wonder what it validates??
@@CurbalEN in background I saw that it is running through every step of every query.
And what is it checking? Syntax?
@@CurbalEN It seems so, yes. Also maybe looking for errors after applying queries on data.
Happy dataflow user here. I wasn't very aware of all the workarounds you mentioned that I silently implemented myself. Anyway, room for improvement and for MS!
Power Desktop = Development. Got it!
Thank you for saving me so much time :D
Correct :)
You have summed all my frustrations out so well. Earlier during my research phase, I was really convinced that Dataflows would make the entire process easier. I quickly reverted to Desktop. It's a really painful experience.
If you develop in the desktop then 50% of the troubles will go away and the end result is neat. Don’t give up!
Okay, I love dataflows - but all the issues you raise in the video are spot on! Especially the "Validating Queries" issue. The absolute worst. That gets a Golden Turkey for UX... but supposedly they are going to roll out a fix that allows you to save your work and let the validation run in the background.
Oh, that would be brilliant actually, fingers crossed!
Dataflows had so much potential but are extremely frustrating to work with as you pointed out. I'm hoping they improve quickly as we'd like to leverage them more. I've run into everything you mention here over and over again. Great overview.
I love the output, but to get there is quite painful. Fingers crossed they fix it!
@@CurbalEN Fingers crossed for sure! The other pain you didn't touch on is promotion from test environments to production. Dataflows aren't in pipelines yet so it's a MAJOR pain to ever make updates to Dataflows once they are in production. You can't simply do the develop in desktop trick as easy anymore so it's a mess to say the least. There are some other things I'd add but that is a huge one.
Great point, thanks!
Hi ! There is any update about all the issues you mention in your video ? I would like to build my first dataflows to schedule them as I noticed this is a huge advantage. But it seems to have many issues and everything is fine on my desktop . It's still happening? Or I should keep developing all on desktop ?
Hi I have recently added few columns to my data in excel, however when I refresh data I get errors. I could only resolve by reconnect the data source & reconfigure all dashboards & visuals. Is there any short cut way to achieve this.
Hi Ruth, thanks for the tips. I didn't know the datatype requirement and the automatic conversion of errors to null. Also, I agree that the validation queries process could be clearer. Beside that, the migration of ETL from PQ Desktop to dataflow is being useful for me, mostly in the development phase: I splitted the dataflow into two, a staging and transformation one, so I can pick the tables at the phase I need.
Dataflows are great, is the implementation that is not good at the moment.
I transferred almost everything on dataflows except for mapping because merge needs premium license. But it all works well for me. Yes in the beginning i had trouble of setting up gateways but after that it all worked smoothly. I couldnt have made those reports that i wanted in PBI if there werent for dataflows. Now, thank God, it all works as it is supposed to :)
Yes, once you set it up it works beautifully 😍
Thanks for sharing this! I'm experiencing the same issues. Very frustrating!
My tricks should smooth out the issues, give them a go!
Thanks Ruth for this wonderful video and realistic feedback. I have recently started to dig deeper into dataflows and was looking forward to using them extensively. Are these issues/annoyances still around, especially the ones on slowness and error messages?
It is much, much better now, but there are some annoyances left. Should do an update video!
Hi Ruth
This is an interesting one. I know it sometimes takes along time to validate the queries in a dataflow, but I have never come across the problem of missing the chance to hit refresh. Could this be because of different tenant locations having different behaviours? Maybe I have misunderstood that one.
What I do when developing a new entity that I want to put in an existing dataflow is create a new dataflow, get it working in there and then copy the code to the existing dataflow.
I’m sure I have done an entity with the Any data type, but will check.
Thanks for this!
Ollie
I agree with all of these, I haven't done much with PowerBI data flows but it is exactly the same issues with CDS (dataverse) data flows. It kills me to see how slow it is 😂. Thanks for sharing this info.
Hope it helps in the future!
Thanks for the honest review. MSFT would describe all these limitations as "features" lol.
Hopefully they will fix all that soon :)
Hello Sir, Please help me, I want to import data from apache druid database but not found any options to connectivity. Please suggest me.
Thanks for the video !!!
One of my most frustating error is that when I have like 5 queries ( in a single data flow), then I add 1 or 2 more, and the refresh simply stop working... is always the same problem “ SharePoint failed request.. bla bla bla”
Hehe hope Microsoft can put some effort in making data flow a better tool!
Ruth, I'm Javier... I took early retirement 2 years ago. So this is the reason you could miss me. I started talking to you about Dataflows when you didn't know them... :-) . I had high hopes with Dataflows to automate in the cloud many things in my company. Now seeing the problems you show I thank God not to be there. That kind of problems are the ones that make you despair. One would expect to be able to migrate practically your PQ developments to the cloud with almost no problems.... but I see that the problems are huge! Congratulations for your work! You are a very clever Spaniard!
Hi Javier, I was really confused when they announced dataflows and I am still confused for a new set of reasons 🤣🤣
To be fair, once you set them up, they are heaven sent! Wish excel could consume them :(
@@CurbalEN What I had in mind was that with Dataflows any employee would have at his disposal a "Mainframe" with its computing power both with its on-line and batch processing capacity to run processes in the cloud whenever he wanted. I was delighted with the computing power of PQ and the efficiency of a language like M. But I was disappointed with the lack of scalability that you mention with the M language generated by PQ. I am now a mere spectator, but I hope Microsoft will rectify this issue. I loved the building they were doing with Azure..... But scalability from Excel is crucial!
I am sure they will in time:), so much to fix... can you imagine?
hahaha i had the same reaction when i notice the syntax differences alert.
Crazy eh? 🤷♀️
Awesome as usual!!! Thanks a lot!
Validation in DF SUCKS!!!
Haven't read about this before, so I thought it was just me!
Yes! This video is 100% spot on! I am trying to move all my queries from PBI Desktop to Dataflow and its just sooo infuriating! Not only is it so slow to do the simplest of queries, there's so many messages without any explanation, and on top of that, you have to be so careful that you have the right license for each query! I have PBI Pro and I did a bunch of merging. in the end I get an error message saying nothing will be refreshed if I don't upgrade to premium....
Hopefully they will address those concerns soon!
I can really relate to your frustrations! I'm trying to move all from PBID to Dataflows and getting frustrating errors like "On-Prem execution not supported for entity" even though I'm working on a Premium workspace!
Thanks for the video, definitely affirms my frustrations. I especially agree with wasted resources due to Premium requirements. Allowing all desktop transformations in dataflows would save Microsoft's resources so those steps don't have to be reprocessed in multiple datasets! So frustrating.
Fingers crossed they will fix it!
One of the big problems is when the validating process is so slow that the connection broke and we lose all changes done!!
🥴🥴 Sorry to hear, that is new to me!
Nice to see that I'm not the only one who is a bit frustrated about the PBI Dataflows.
In relation to: "01:30 Managing gateways ...or not"
Do you know if it is possible to connect to an Amazon Redshift Database, without a gateway?
There are ways to get rid of the gateways, for example migrating the data to a non-gateway source. Sometimes it makes no sense to do that though..
Power Query in the cloud is a great idea. The execution (as of 2024) is half-baked. Using dataflows still feels like using a pre-release beta release.
I use dataflows a lot, really, a lot, and I do hate them with a passion for most of the reasons you mention but, I have never had an error for different syntax.
I am about to explore dataflows. just wondering if any of the issues mentioned in this video have been resolved. if not I may wait a little longer before trying dataflows
Some have, some not, but regardless don’t wait. Dataflows are a pain to set up but amazing once you have them working. Do the dev work on the desktop and you will be fine.
Thanks for great sharing. I felt the same pain. I find many value to use dataflow for me but I also find additional frustration like 1. Dataflow failure and stop running but it linked dataflow still remain running. 2.
Manually cancel dataflow refresh show status as cancelling (where I confuse whether cancel done and ready for me to run again refresh). 3. It is a waste where incremental refresh cannot work for power BI dataset with data source is dataflow.
Also dataflow and power BI dataset refresh are independent today, so some time my power BI dataset is start refreshing but dataflow is still running.
So you connect a dataflow with a dataset?
For the syntax error, I found my trick I do in desktop to get my custom sql statements to not have the (#lf) junk in the Advanced Query Editor works to bypass the red syntax issue. I'm not sure if this will work in other use cases, but for the literal string for [Query= "SELECT ..."] I actually concatenate two strings together. I found this worked on accident because I was putting parameters in my query and found that it bypassed the nuisance formatting text and retained a human readable display. Turns out you don't need to use the parameter you just need to concatenate two strings together in you query so now I just do [Query = "" & "SELECT stuff as column FROM table"] and it displays all my hard returns, tabs and spaces correctly. And this little trick seems to get me out of that obnoxious and often seemingly erroneous syntax error check as well.
Didn't know that either, thanks again!
Hi Ruth, fantastic video as always! I got surprised when you said that there are ways to merge tables without premium...do you mind to point me in the correct direction? Muchísimas gracias!
Hi Pilar,
Just disable one of the merged tables ;)
That is all you need!
@@CurbalEN thanks a million I will test it right now ;)
@@CurbalEN I didn't know that was all that it needed.... so I was putting both tables into the same query and then merging (or appending) back to the previous step that represented my other table's output.
It truly works, test it!
@@CurbalEN It work perfectly and improve so so much the performance of my report but I have a question regarding the security options.. Do you know where I can learn the implications of merging 2 different SQL queries (which are coming from the same database)?
Hi Ruth, i stopped using dataflows as it was difficult to know when a run failed e.g. an account's password changed which you would expect to cause a refresh failure (like it does within the pbi service if your dataset is pulling directly from a source); however i found that the scheduled flow would report a successful run even though it was not able to pull data from the source due to an authentication (password change) issue. Also, it was not yet possible to have the pbi schedule trigger the flow refresh ... so, like you do, you schedule the dataflow to refresh an hour before you set your pbi dataset to refresh. but, as the client's dataset grew so the dataflow refresh would take longer to refresh ... instead of getting partial data back from flow, you get nothing (this is both a blessing and well, not a blessing). bottom-line: i had to remove the dataflows at a client site as the learning curb to educate them into where to check and how to check for successful-failures was a step too far. A shame really, as the dataflow was able to prepare the data astoundingly faster than the pbi service alone. sob sob.
I feel you as I continue struggling with dataflows. I am on the verge like you: should I keep it or should I scrape it,but for now the benefits are outweighting the concerns. That might change when the development is done and my customers have to maintain them. You are worrying me now 😞😞
@@CurbalEN oh and there is a hard 2hr refresh limit (which is also a blessing, until its not).
Agree, if it takes 2hrs to update, incremental refresh would make more sense. Wish it was available on pro though 😥
"You have an error" sounds like my maths teacher from college. She used say that and turn away 🙄🤣🤯
Lucky you then! You were already trained for this situations 😂😂
Is it possible to use Python in the dataflow to transform data? Thanks
Not in the power bi dataflow, but you can in fabric
@@CurbalEN Ok, Thanks. The problem ot the fabric it's the price...
@tiago5a yeah, i know :(
Maybe you can use python in excel and make it work?
Hi, thanks for the video. As you told us, the fact that dataflow doesn't support incremental refresh is really strange.
For me, the main reason why I use dataflow is to minimize the refreshes, what is a good deal for me and for Microsoft.
If Microsoft allows pro licenses to use incremental refresh on dataflow, they will probably save servers availibility (and so save money)
Exactly!! I agree!
This has changed, you can now to IRs on Dataflows.
Thanks for your video. Dataflows are kind of annyoing to devs. I think the validating thing does check whether the calculated tables are still valid after applying the changes. Sometimes I have to implement a change in multiple steps to ensure that the calculated tables are valid all the time.
I am not sure about what I need to do to pass the validation, is it data types you check?
@@CurbalEN Sorry for awaking hopes :-(
I Stopped a validation process after 1,5h waiting a couple of minutes ago.
I think the validation process tries to check if calculated tables which derive from the one you change will still be valid after the change.
I did not check this till now, cause every change just needs so much time ..
😭😭😭
Sorry to hear !!
Oh. My. Gosh. I was literally just banging my head against the CSV problem!!! Data comes into PQO beautifully from a OneDrive stored CSV, steps are recorded, save and close, REFRESH ERROR! Whhhhyyyyyyyyy !!!!!??????
🤷♀️🤷♀️🤷♀️
Do all development on the desktop for now, it will save you a lot of headaches!
@curbal are these problems fixed as if now or fo they still exists
The video is only one month old, so they are still there but if you follow my tips you can enjoy a heavenly data flow :)
@@CurbalEN i have million record csv residung on local network whuch is output from legacy system so on prem gateway is the soultion i see which cant be avoided, unless i use azure adf to copy csvs inyo azure sql . what solution yiu think might hrlp my case
@@imranpearl Hi Imran, I am today having same problem that you asked in above comment , how you managed to do further? appreciate any word
Dang. Wish I'd seen this video 2 days ago. Would have saved me some major frustrations by not even attempting PQO, but rather going straight to PBID.
I was shocked at how many of your frustrations I related to.
I hope MS can work the bugs out of this, quickly. So much potential, but in the end it just made me come close to losing my religion.
They say, if you find yourself in hell, keep walking! The road is bumpy but the destination worth it, once you set them up, they are a lifesaver!!
Glad I made the video even if it was too late for you :(
You should use and build model.json schema file/s in the data lake so you don't need to set data types within PBI.
Great point, I havent got that far yet!
@@CurbalEN The one thing I don’t understand is they allow your own external ADLS CDM storage but provide no tool to say convert sql table schema to auto build a model.json file. Lots of manual work. I just ran a dataflow from sql tables and grabbed the output model.json file power bi created on the lake. Not ideal but was the quickest solution. Bring on the tools.
I have to yet play with the json files, so I dont have any meaningful feedback there, but know I have yours, thanks for sharing :)
...Also frustrating, dataflows don't load in parallel. They run sequentially. This is only good in the sense that I can have a Dataflow Start and a Dataflow End query at the beginning and end of my flow which will allow me to monitor the time it takes for the flows to complete in a Power BI report. BUT, on top of only running sequentially, if you have a queries that normally completes within a few milliseconds, you have to wait at a minimum 31-ish seconds per query before the next query in the flow fires. So if you have a flow that has many small tables, your flows time to completion can really inflate. I didn't know this starting out using dataflows and I had a little over 20 tables in one flow and it was taking over 30 minutes to complete. I ended up breaking that flow up into multiple smaller flows which ended up kind of approximating the far superior parallel load you get in a dataset. This would be less of an issue if you could "DirectQuery" a dataset to a dataflow... but you can't. So if you have an dataset refresh scheduled for 30 minutes after your dataflow refresh... you may be getting the prior dataflows refreshed data not the intended data.
Oh, I dind know that! Thanks for letting me know!
Sorry I couldn't get how you managed to create your own Dataflow?
You mean my own data lake?
m.czcams.com/video/g73TwnjjTZw/video.html
Very same experience :(
Hope the tricks mentioned will help!
I've never seen Ruth so frustrated. The video actually depressed me. I wasn't prepared for this.
The output is worth it though, but patience is required
@@CurbalEN patient of a saint I think 😊 Seen the Dataflows added into Teams now…oh joy 🤩🙁😆
Patience training is good for your soul 😂😂
First i love it,... but them..""##$%%(== .. not all that glitters is gold!
😂😂
It is worth the hassel! Once you configure them they just work ;)
I still don't get what the point of data flows is.
Dataflows allow you to clean your data and store the results in a data lake ao people can acces it for analysis.
@@CurbalEN so basically another data modeling option in addition to SSAS/AAS tabular models, but created in power query instead of visual studio or tabular editor?
Yes, you got it!!
Yes all of these Dataflow quirks are super annoying, also you can DirectQuery into your Dataflow. However it does not work if you have ' in the name of table. It does not tell you that there is an invalid character in your table name, it will simply not allow you to DirectQuery into your dataflow. Also you cannot access the error message through the API, super annoying.
I know! The editing experience is horrendous:(
no RLS on dataflows is annoying.
This got my interest Robert. How would it work/why do you need it? Wouldn’t the RLS in the report dataset handle that?
@@oliveroshea5765 I've found that when you create reports off of datasets you don't get the flexibility that dataflows give. As the data model is already pre-defined in datasets. With dataflows, with RLS enabled means users can get raw access to data across various BUs where restrictions could just be applied. The user can then make their own data models / datasets.
You need to post a trigger warning on this video for people that have struggled with...I mean used....dataflows. So dead on.
😂😂 I should, shouldn't I?
btw - best way I have found to track down errors in dataflows is to take the PQ back to Power BI Desktop and use the 'Keep Errors' option under the 'Keep Rows' button. The profiling tools are great to show columns with errors but they take forever to load.
100% this...haha.
Agreed, that is the only way to do it, I think...
I want Matthiew Roche respond to that video so bad. I mean the keep adding those fancy features to dataflows and not to desktop, having fundamentals you mention not adressed.
Hopefully they will address this things soon!
@@CurbalEN Well I started to use Dataflows recently and to give it some justice for me it was very smooth. Well the errors/preview loading/validation are very valid, but once I started to use linked/computed entities more and more it was much better. Benefits:
+ I found it the best sharing experience with other power users/developers (no tokens, virtual machines, passwords etc.)
+ makes the model very clean after all (when its already complicated after complex RLS, Aggregation Tables, Disconnected Tables, Calculation Groups)
+ Group By, Append, Merge are big no in PQ Desktop. Now I don't need to ask Azure Data Engineer or DB Owner to create those
+ UI/UX
+ I actually like forcing to decide on data type
+ Troubleshooting refresh failure, especially dimensions
+ Dataflow failure =/= Dataset refresh failure
+ Easier to managed credentials
+ Descriptions of steps can be longer (love the icons)
What I miss:
- Automatic refresh trigger between entities from different workspaces
- Need to setup Power Automate to orchestrate Dataset refresh
- Validation/Errors/Preview Loading :)
- Dataflows connector in Excel (coming fortunately)
- RLS directly in Dataflow
In terms of building data culture it's much better than shared dataset, where development of report is quite hectic (still no visible DAX code!)
As I said on the video, Dataflows are a good thing and should be used, but the configuration process needs some serious work, so the purpose of the video was:
1. Don't get discouraged by the roadblocks
2. Use my tips to easy the pain
3. Fingers crossed they adress the pain points and fix them.
My frustration for some of the paintpoints might have given you the impression that I discourage the use of dataflows, but I assure you that is not the case.
Best tip "do not use" XD
No, no, it is worth using. Once everything has been set up it works beautifully.
@@CurbalEN there is so many more professional and dedicated tools for ELT/ETL and orchestrations, like for example Data Factory and Azure SQL or Synapse. All in one just doesn't work, the best example is Access or Excel.
I havent used Data factory or synapse, so I can't compare. If they fix all those bugs, dataflows are great imo
@@CurbalEN my opinion is referring to enterprise companies but for small all in one probably can work like Excel ;)
True, I have been doing this for medium sized companies and the change is remarkable!