Comments for Excelguru

Comment on Check the application version in Modern Office by Ken Puls

Ken Puls — Tue, 09 Jul 2024 16:53:51 +0000

In reply to Junior.

Hi Junior,

I'll admit, I gave up on this one due to the finicky nature of the registry. It was like playing whack-a-mole to try and get all the SKUs Microsoft sells.

Instead, I test if WorksheetFunction.Xlookup(1, 1, 1). If it does, it is 2021 or higher, if not it is 2019 or earlier.
If WorksheetFunction.Concat(1) returns a result it is 2019 or higher, but if it returns an error, it is 2016 or earlier.

It's cludgy, but if allows building a case structure. The tricky part is finding the functions that exist on either side of the major version cutoffs. 😉

Comment on Check the application version in Modern Office by Junior

Junior — Tue, 09 Jul 2024 12:11:35 +0000

Hey Ken, i know it´s been a while.
I´m trying to distinguish 2016 and 2019 (further 365) for my application, but my installation (Microsoft Office Professional Plus 2019) returns "2016"

Is there any update on this?

Comment on Using getSize with RibbonX buttons by Richard Shaw

Richard Shaw — Tue, 25 Jun 2024 07:03:27 +0000

Hi Ken, just want to say thank you, this problem haunted me for a while.

Comment on Displaying “Last Updated” Date And Time In PowerPivot by Display Last Refreshed Date in Power BI - Excelguru

Display Last Refreshed Date in Power BI - Excelguru — Tue, 11 Jun 2024 22:57:53 +0000

[…] actually covered this topic in the past for Power Pivot solutions. You can find that article here if you’re […]

Comment on Sharing Power BI Dashboards by Can users see my raw data in Power BI? - Excelguru

Can users see my raw data in Power BI? - Excelguru — Tue, 11 Jun 2024 22:54:08 +0000

[…] BI meetup in Vancouver, Peter Myers was demoing the feature to create a public facing web page (as I showed in this post.) He quickly took his raw data, threw together a few visuals (without writing a single custom […]

Comment on Unfill in Power Query by Sherry Fox

Sherry Fox — Tue, 11 Jun 2024 14:20:20 +0000

Thanks so much for sharing this detailed tutorial. I was watching the YouTube video, but got stuck on the "Index". My attempt was by adding an Index, rather than a custom column WITH a formula like you did. Thanks for having this tutorial that SHOWED the foruma I got stuck on. This was exactly what I needed to accomplish.

Comment on A message to forum cross posters by Kelcher

Kelcher — Tue, 28 May 2024 19:12:21 +0000

Thank you for explaining to me the mistake I made what affects the specialists who contribute with the help they give me and the consequences that this behavior can have. I apologize to everyone and promise it won't happen again, I understood that perfectly. And I thank you for the great will in the relief work.

Comment on Date Formats in Power Query by Ken Puls

Ken Puls — Wed, 22 May 2024 16:39:48 +0000

In reply to Himanshu. You'll need to delete the initial ChangedType step that Power Query generated for you automatically (near the top of the query). Then go to Change Type -> With Locale. Set it to a Date and define it as English -> United Kingdom. That should get the right conversion for you. Right not it is trying to apply an English US format by default, which is interpreting your data as MDY instead of DMY. Note that you MUST do this before any other Changed Type step.

Comment on Date Formats in Power Query by Himanshu

Himanshu — Wed, 22 May 2024 14:55:41 +0000

In my excel file, the given date is 1-sep-20, but in the power query, it shows 9-jan-20, but I need 1-sep-20. How do I do it?

Comment on Rolling 12 Months in Power Query by Ken Puls

Ken Puls — Wed, 15 May 2024 18:56:50 +0000

Clever Mahmoud! Interestingly, in testing this with 500 rows, the perf difference between your (UI version) and Bill's (M version) solutions are very close (less than 1 sec), where my original is much higher (avg 3.7, median 2, range 1.5-6.9 sec).