classroom microsoft excel symbol commonecation in software in 2022 / 2023 basim simbol you generation operator programmer analis computer 2022 / 2023 speak room 🏛🌍🌎🌏
Cool Video. You mentioned, that the Sharepoint - Excel Connector is missing.... You can just use Sharepoint Folder Connector and then from there access to the Excel File in Powerquery.... That works well.
Big fan of your work ..and love your power query magic videos too.. just noticed that the pq interface of the data flow is bit different from the the desktop excel. Please make a video enlightening us on new features like map to entity, key, AI insights and what else is new and interesting :)
Thanks Sumanth. AI insights is a premium only feature and not something I ever use. Map to entity is again something I don't use but can be used to map your columns to standardised field names if you're using a common data model / Dynamics. For more UI elements info, check out my datalfows video here ruclips.net/video/HXSJXOjtfeE/видео.html
lover your videos always learn something new, is there a difference or benefit instead of doing the query directly from excel, like file size, refresh time for the example showed that didn't required that much changes?
= PowerPlatform.Dataflows(null) pasted into a blank query, in Excel Power Query doesn't seem to bring up dataflows I've created. Any idea when this'll be rolled out for everyone please?
If you open Excel and go to File --> Account it should say what version and channel you are on ( something like 2108 Semi Annual for example.) What does yours say?
Nice tips. I have twisted PQ question. Hoping someone can help me out. How can you purge data in self referencing query? For instance, if I have May-22 data (50 rows of data) and if I reload it again then I want it purge all May-22 data (50 rows of data) and upload current data load for May-22 (10 rows of data). After upload May-22, we should have only 10 rows of data.
Not really dealt with self referencing queries, if you want to replace everything does it need to be self referencing? I might be doing a related video using a Switch to return different applied steps based on a user input that may be relevant... I'd need to think it through and try it out
DateTime.LocalNow() shows the date as yyyy-mm-dd... but Date.From shows m/dd/yyyy. I often have to convert the date to an American date then convert to a proper date, even though we're using LocalNow. This is so annoying. Why can't Americans just accept that their date format is wrong :)
@@AccessAnalytic No, but it's a government computer, so there are lots of settings I can't change. I can set all the date formats in Desktop, but when I publish, the formats change to US. It's a pain 😐
In my case, "es-ES", I transform to mm-dd-yyyy with this: = Text.Combine({Date.ToText(Origen, "MM"), Date.ToText(Origen, "dd"), Date.ToText(Origen, "yyyy") }, "/")
let Origen = Date.From(DateTime.LocalNow()), Datos = Text.Combine({Date.ToText(Origen, "MM"), Date.ToText(Origen, "dd"), Date.ToText(Origen, "yyyy") }, "/") in Datos
Wyn - Your videos are the best! 👍
Too kind Ashutosh. Thanks!
classroom microsoft excel symbol commonecation in software in 2022 / 2023 basim simbol you generation operator programmer analis computer 2022 / 2023 speak room 🏛🌍🌎🌏
DataFlow was entirely new to me, Thank you again for the Data Flow
No problem
I have struggled with IntelliSense and the double-word problem for a while -- no more. Thanks, Wyn!
Great to hear, thanks for letting me know
Cool Video. You mentioned, that the Sharepoint - Excel Connector is missing.... You can just use Sharepoint Folder Connector and then from there access to the Excel File in Powerquery.... That works well.
Thanks Giovanni, yes that would work but can result in a much slower refresh than using the from Web method. ruclips.net/video/vPV67RLGoOg/видео.html
I always liked your little tips, Thank you for sharing with us.
You're welcome Vishal
Nice, wasn't fully aware you could Replace in many columns at once (2:11)! And ALL the tips are good. Best 8 minutes of my Power Query life. :)
Hah.. unintended bonus tip Donald!
Great tips Wyn
Cheers Westley
Awesome tips. Thanks Wyn.
You’re welcome Renier
Great Tip Wyn. Thank you!
You’re welcome Cy
Amazing tips!!!
Thanks Brenno
Big fan of your work ..and love your power query magic videos too.. just noticed that the pq interface of the data flow is bit different from the the desktop excel. Please make a video enlightening us on new features like map to entity, key, AI insights and what else is new and interesting :)
Thanks Sumanth. AI insights is a premium only feature and not something I ever use. Map to entity is again something I don't use but can be used to map your columns to standardised field names if you're using a common data model / Dynamics.
For more UI elements info, check out my datalfows video here ruclips.net/video/HXSJXOjtfeE/видео.html
lover your videos always learn something new, is there a difference or benefit instead of doing the query directly from excel, like file size, refresh time for the example showed that didn't required that much changes?
The main reason is centralisation for use in multiple reports and when data sources are very slow to refresh ( you can also schedule refreshes )
Good job
Thanks Kassio
= PowerPlatform.Dataflows(null) pasted into a blank query, in Excel Power Query doesn't seem to bring up dataflows I've created. Any idea when this'll be rolled out for everyone please?
If you open Excel and go to File --> Account it should say what version and channel you are on ( something like 2108 Semi Annual for example.) What does yours say?
@@AccessAnalytic Professional Plus 2016
Hi @@raneeshifars8168, only Office 365 gets new features. One-off purchases such as Excel 2016, 2019, 2021 never get updates
I guess my company is still on an old version of Excel Online. I have no Get Data options other than "from picture"....
Excel for web doesn't have this feature yet. It's Excel desktop (if you're on Microsoft 365)
Nice tips. I have twisted PQ question. Hoping someone can help me out. How can you purge data in self referencing query? For instance, if I have May-22 data (50 rows of data) and if I reload it again then I want it purge all May-22 data (50 rows of data) and upload current data load for May-22 (10 rows of data). After upload May-22, we should have only 10 rows of data.
Not really dealt with self referencing queries, if you want to replace everything does it need to be self referencing?
I might be doing a related video using a Switch to return different applied steps based on a user input that may be relevant... I'd need to think it through and try it out
You can also type datefr and it will offer date. from
Yep good tip
Wonderfull, no less :-)
Thank you Henrik
Link plz ?
Which link Sameh?
@@AccessAnalytic
To access the website that you used in this video
Hi I’m connecting to my own OneDrive and using PowerBI.com. I can’t share links to those
shift+9 has killed me
🤣😬
another way of getting DateTime.LocalNow is to type =dtl enter key ( i saw this in a presentation by Melissa de Korte )
Yep nice one
DateTime.LocalNow() shows the date as yyyy-mm-dd... but Date.From shows m/dd/yyyy. I often have to convert the date to an American date then convert to a proper date, even though we're using LocalNow. This is so annoying. Why can't Americans just accept that their date format is wrong :)
Is you windows setting set to US format? If you look at the time and date in the bottom corner of your task bar does it show m/dd/yyyy?
@@AccessAnalytic No, but it's a government computer, so there are lots of settings I can't change. I can set all the date formats in Desktop, but when I publish, the formats change to US. It's a pain 😐
That’s a shame, I wonder if this thread may help community.powerbi.com/t5/Service/Date-format-Desktop-vs-Service/m-p/91203#M15771
In my case, "es-ES", I transform to mm-dd-yyyy with this: = Text.Combine({Date.ToText(Origen, "MM"), Date.ToText(Origen, "dd"), Date.ToText(Origen, "yyyy") }, "/")
let
Origen = Date.From(DateTime.LocalNow()),
Datos = Text.Combine({Date.ToText(Origen, "MM"), Date.ToText(Origen, "dd"), Date.ToText(Origen, "yyyy") }, "/")
in
Datos