Ken Puls’ Post

In this article about multi condition logic in #PowerQuery, Ken Puls explores a scenario where we need to choose between several options. In #Excel, we would probably separate these options into a separate table, then use VLOOKUP to pull the appropriate value into the table. However, because Power Query doesn't have a VLOOKUP function, Ken builds a CHOOSE function (or SWITCH if you prefer #PowerPivot’s #DAX version). 💡 See how it's done: https://lnkd.in/gvgUTvGc

A while ago, I was asked for help with an #Excel #PowerPivot model. The person had built a model and wanted to add slicers for value fields. In other words, they’d built the #DAX required to generate their output, and wanted to use those values in their slicers, which you can’t do. Except maybe you can…? 🔥My approach to solve this issue is to use #PowerQuery to load the tables, so I can then re-shape the data and load it into the #DataModel. Here's how I did it: https://lnkd.in/gFEmFsdX

A while ago, Ken Puls was asked for help with an #Excel #PowerPivot model. The person had built a model and wanted to add slicers for value fields. In other words, they’d built the #DAX required to generate their output, and wanted to use those values in their slicers, which you can’t do. Except maybe you can…? 🤔Ken's approach to solve this issue is to use #PowerQuery to load the tables, so he can then re-shape the data and load it into the #DataModel. Here's how he did it: https://lnkd.in/g-xw5G3X #BusinessIntelligence #DataModeling #DataAnalysis

A while ago, Ken Puls was asked for help with an #Excel #PowerPivot model. The person had built a model and wanted to add slicers for value fields. In other words, they’d built the #DAX required to generate their output, and wanted to use those values in their slicers, which you can’t do. Except maybe you can…? 🔥Ken's approach to solve this issue is to use #PowerQuery to load the tables, so he can then re-shape the data and load it into the #DataModel. Here's how he did it: https://lnkd.in/g-xw5G3X

A while ago, I was presented with an interesting problem: how to create running totals in #Excel. We could use a Pivot Table to create running totals, but what if we want to preserve the original list? We could also set up a formula on our table that summed the row above plus the row to the left, for example. Of course that means you MUST land it in the worksheet, which may not be ideal either. 🧐The solution is entirely possible to solve with #PowerQuery, but with no one click feature in the user interface, I'll walk you through how to pull it off: https://lnkd.in/gD2RqWTU

To access your SQL database, you want a tool such as Superset or PowerBI for your daily data analysis. With the PowerBI Q&A feature, you can ask questions in simple English to get the results you need. No need for SQL queries. It´s simple: 1. Connect your database and import tables 2. Review names and columns of your tables (this is done in PowerBI only, no changes to existing connections) 3. Enable Q&A and start playing with your data. All VATify users can connect to their SQL data and get hundreds of queries for their daily work. Reach out to us for a demo! #SQL #VATcompliance #tax #Internationaltax #taxtechnology

In #financefunctions across all size businesses, I have come across the most power-user, ninja warrior #excel super users there are. Even more skilled than traders on London trading floors that I worked for. The #powerquery, VBA, formulas are astounding and in short they work. ****#Xero Consolidations of 80+ entities with transaction data ****Lookups to immensely custom eliminations/adjustments/FX ****Presentation sheets that are jaw-dropping However, it takes; ***Blood, sweat and tears ***The spreadsheets are fairly brittle, i.e. they break easily (that's a real term in software) ***The key dependency on the creator is significant and the real factor in progressing further reporting The good news; ***There is no alternative to figuring out the workings manually ***It's an effective way to share manual iterations with stakeholders and confirm value as the report is developed ***With a deep understanding of the workings it can be refactored into a better automation layer Every single customer of ours, all complex, all talented, has been through this journey. And when they understand that they can replace reams of complex #powerquery with one SQL View that; ***can be re-used between multiple reports ***means report creation is far faster ***report maintenance is massively reduced ***report rendering is fast ***view can be use by #powerbi, #looker, #tableau and #googlesheets They literally say "I want that". So this is why we GIVE away years of expertise in our #xeroconsolidation SQL Templates in our platform. Even to Free Trial users. Possibly not the best business move (we could charge $1k's) but automating an existing complex Excel report from one automated table... it's priceless and it's a big part of our why. High fives from the part-time report creators that get a big part of their day back. YouTube link in the first comment! #sqlviewpatternsandpractices

A while ago, Ken Puls was asked how to use #PowerQuery to number rows by group, essentially adding a column to the table with index numbers sorted by and restarting with each group. He wrote up this article to explain how to do this: https://lnkd.in/gacKez3H #Excel #PowerBI #DataAnalysis #DataAnalytics #BusinessIntelligence

A while ago, Ken Puls was presented with an interesting problem: how to create running totals in #Excel. We could use a Pivot Table to create running totals, but what if we want to preserve the original list? We could also set up a formula on our table that summed the row above plus the row to the left, for example. Of course that means you MUST land it in the worksheet, which may not be ideal either. 🧐The solution is entirely possible to solve with #PowerQuery, but with no one click feature in the user interface, Ken walks you through how to pull it off: https://lnkd.in/gvJNjW7V

A while ago, Ken Puls was presented with an interesting problem: how to create running totals in #Excel. We could use a Pivot Table to create running totals, but what if we want to preserve the original list? We could also set up a formula on our table that summed the row above plus the row to the left, for example. Of course that means you MUST land it in the worksheet, which may not be ideal either. 🧐The solution is entirely possible to solve with #PowerQuery, but with no one click feature in the user interface, Ken walks you through how to pull it off: https://lnkd.in/gvJNjW7V