How I used Powerquery M for Excel queries | Chris A. posted on the topic | LinkedIn (2024)

#PowerQuery is an excellent tool for getting and manipulating data. Excellent set of connectors and a powerful programming language with a useful menu driven front end to get you started. So you have captured data - where to next? Thing is, I get that #Excel has PowerPivot and a data model tool, but actually I want to use a database with SQL, and I want it to be an end user tool rather than a corporate data centre. And I want to use the data I have manipulated. Which to my mind means Access. Actually I could use VBA and the Jet database engine that lives within Excel, but I like the Access UI. Of course Access doesn't have PowerQuery, although I could use SQL. But in fact data ETL is often better done in a tool like PowerQuery, with its M language. And Acess does offer me good connectivity.So come on #Microsoft. Stop hobbling me. Make these things work better together. #Access remains a wonderful end user desktop database. Embrace it, and bundle it within #Excel!

20

11 Comments

I researched the DateTime.ToText function in #powerquery , here's what I learned. 𝗕𝗮𝘀𝗶𝗰𝘀By default, the DateTime.ToText function uses the culture of your system to transform a datetime value to text. That means the output can differ depending on where your query refreshes. 𝗙𝗼𝗿𝗺𝗮𝘁You can output a specific format by providing a formatting string. These can be rather cryptic. The updated article on powerquery[dot]how now contains an overview with all relevant combinations. Some fun examples: Turning "12/31/2023 1:30:25 AM" into text can result in: t - "1:30 AM"T - "1:30:25 AM"r - "Sun, 31 Dec 2023 01:30:25 GMT"F - "Sunday, December 31, 2023 1:30:25 AM"You can find all other variations in the attached picture. 𝗖𝘂𝗹𝘁𝘂𝗿𝗲The output formats can change depending on the specific local cultures. nl-NL: uses dd-mm-yyyyen-US: uses mm/dd/yyyynl-NL: no AM/PM notationen-US: uses AM/PMFor all other differences, find the article at #powerqueryhow .Happy tuesday!PS. If this was useful, consider reposting so others can follow along! ♻️

213

23 Comments

Interesting investigation into the PowerQuery DateTime.ToText function

28

1 Comment

Here are 9 little things (hacks) in #PowerBI. 🚀 Tell me which one is your favorite. 😍Video link in the comments. Enjoy!#DAX #PowerQuery #visualization

121

5 Comments

Learn how to reformat all of your #PowerPivot #DAX formulas by calling the SQLBI DAX Formatter with a little VBA.This is a follow up to a previous post where I called an API to reformat all of my #powerquery code in Excel.I know a lot of people are probably trying to figure out #Fabric and are asking, "Why?".Personally, for quick/ad hoc analysis that does not need a refresh, I am going to try and use Excel if I can. Also, I often have to provide code and documentation so that someone could reproduce it later on. I can code in #Python and have been learning a little #R, but often for these kinds of analysis I am working with someone that has neither installed and does not have experience coding in either one.At the very least, if I can stay in Excel, I know they have it installed, and I can more easily walk them through it. REMINDER:#VizForVets has our monthly meeting with Albert Bellamy set to present this Wednesday (June 21st) at 6pm ET. RVSP link in the comments. You don't want to miss it, he always speaks very candidly and boils things down for people so that you have the right x, y, z to make things happen. If you want to build your brand and reach, join us.

16

2 Comments

Want to return multiple elements from a list based on their index positions? And wondering if #powerquery has a built-in function for this? 🔍Sadly, the M language says ‘No’. 🚫🛠 Real-World Scenarios:🔶 Let's say you want to rename the 3rd and 5th columns in your query without having to specify the names manually.🔶Or what if you have a function like List.PositionsOf that returns you the index numbers of the desired characters you want to retrieve.In both scenarios, a custom function is your knight in armour 🛡. And I discovered that the List.Transform function can be a lifesaver in this regard.📝 I share with you 3 methods to get positions from a list. 🎁 BONUS: Take my custom function for this task.🔗 Discover these methods and the custom function here: https://lnkd.in/eCydvh37

108

6 Comments

Let me add some value to your lives by sharing a PDF Guide that shows some of the common features that 𝐏𝐨𝐰𝐞𝐫 𝐁𝐈 𝐃𝐀𝐗 𝐚𝐧𝐝 𝐏𝐨𝐰𝐞𝐫 𝐐𝐮𝐞𝐫𝐲 Offer.In this guide, we take a simple '𝘚𝘢𝘭𝘦𝘴𝘋𝘢𝘵𝘢' dataset as an example. 💪 With 𝐏𝐨𝐰𝐞𝐫 𝐐𝐮𝐞𝐫𝐲, you can seamlessly filter, add new columns, or merge tables using a GUI. 😮 𝐃𝐀𝐗, on the other hand, is your go-to for sophisticated calculations and aggregations.🔍 𝐁𝐮𝐭 𝐡𝐞𝐫𝐞’𝐬 𝐭𝐡𝐞 𝐫𝐞𝐚𝐥 𝐪𝐮𝐞𝐬𝐭𝐢𝐨𝐧: 𝐖𝐡𝐢𝐜𝐡 𝐭𝐨𝐨𝐥 𝐝𝐨 𝐲𝐨𝐮 𝐫𝐞𝐚𝐜𝐡 𝐟𝐨𝐫 𝐰𝐡𝐞𝐧 𝐟𝐚𝐜𝐞𝐝 𝐰𝐢𝐭𝐡 𝐚 𝐜𝐨𝐦𝐩𝐥𝐞𝐱 𝐝𝐚𝐭𝐚 𝐜𝐡𝐚𝐥𝐥𝐞𝐧𝐠𝐞?Kind Regards,Fassahat#powerbi #powerquery #dax #daxfunctions

5

2 Comments

Here is an interesting #PowerQuery problem.I've got 2 tables.Table1 - Contains comma delimited letters. Each letter specifying a day of the week.Day Reference Table - Day of the week defined for each letter.Can you generate the expected Output? 🤎Brownies for making it dynamic. 😁

180

32 Comments

QuoteStyle.None vs QuoteStyle.Csv. This #powerquery enumeration puzzled me all weekend. 🥲 Until I finally found out this.𝐖𝐡𝐞𝐧 𝐢𝐭'𝐬 𝐮𝐬𝐞𝐝QuoteStyle is a relevant enumeration related to working with text. It's used for + splitting strings+ combining columns+ extracting data from files. 𝐖𝐡𝐚𝐭 𝐢𝐭 𝐝𝐨𝐞𝐬In most cases when changing these values around, the results are identical. So I puzzled around, clueless of when it made a difference. But then I found out this. In some strings you may have double quotes. For example: 💠"This is a ""string, in quotes"""💠 "He came to me and asked: """Where are you going?"""."QuoteStyle then determines whether to ignore content between the double-quotes or whether to respect it. The screenshot shows exactly the impact that has. You won't often need it, but now you can at least find the examples at PowerQuery How: https://lnkd.in/ea3EqTinPS. How was your weekend?#bigorilla #powerqueryeverything #powerqueryhow

69

17 Comments

I have tested a simple Deneb custom visual on a Cherry Blossom Data Challenge and it turns out great.Deneb is a certified custom visual for #microsoftpowerbi, which allows developers to use the declarative JSON syntax of the Vega or Vega-Lite languages to create their own data visualizations.This is similar to the approaches used for creating R and Python visuals in Power BI, with the following additional benefits:◾ Libraries are packaged with the visual, so no additional dependencies on local libraries or gateways for your end-users when publishing reports.◾ Specifications are rendered inside the Power BI client, rather than being delegated to another location, typically resulting in faster render times for end-users.◾ Built for the web, meaning that it's possible to integrate with Power BI's interactivity features, with some additional setup.#deneb #datachallenge #powerbideveloper

1