Power Query Trick: Remove (Only) Leading/Trailing Zeroes From Text

Just before the holidays start, I'd like to share this little trick I came across in Power Query.

I recently had the need to remove zeroes (but it can be any character) from the beginning of a column.
Turns out there's a perfect function for that :-)

The documentation isn't totally clear about this, but for the 2nd parameter (trim) you can add a character you want to have trimmed. The description only mentions whitespace, but you can enter any character in that parameter.

On a side note, I got this nice documentation page inside Power Query by using the #shared function, which I blogged about here.

Text.Trim[Start/End]

And in case you might need it, there's also a Text.Trim and Text.TrimEnd function:

Now onto an example, I created a text column with some values via Enter Data:

I have some combinations of text, numbers, leading and/or trailing zeroes and spaces.
For my 1st example I used Text.TrimStart to remove (only) leading zeroes.
I added a custom column and used the UI to add it like this:

This gives me this nice result:

Only zeroes at the start are trimmed, no spaces or zeroes at other places. Great, exactly what I needed!

I also had a look at the other functions and their workings I mentioned:

In case you want to test it yourself, I put the M code I used in my GitHub.

Closing

I hope you found this one useful, let me know if you already use this or are going to in the future!

This (hopefully) might not be my last post of the year 😀, but just to be sure:

Merry Christmas 🎄🎅, a great new year 🎆 and happy holidays to you all!

Back In Business: Power BI Next Step

So, I did a presentation last Friday on Query Folding in Power BI.
It was at the Power BI Next Step conference.
You might think it was another online, like the 2.463.345th, webinar via Teams. But...

WE WERE IN COPENHAGEN!! 😁

Meeting people again, having a chat and drink with other speakers, attendees and #pbifamily friends I haven't seen other then online... It was A-MA-ZING to be back at it! 

Because a few months ago there was still some uncertainty about all the rules, Marc, his girlfriend and me decided to take a road trip and go by car, which was pretty doable and actually quite fun.

I also went for a little run just after the conference!

We also had a lovely speaker dinner in the oldest restaurant in Copenhagen: Det Lille Apotek:

Finally, a speaking dinner again! 😀🥳 #PowerBINextStep @pkamasani pic.twitter.com/G37M5P9tIA

— Nicky van Vroenhoven (@NickyvV) September 17, 2021

And on Saturday we finished it off with a little boat trip:

Out on a lovely boat trip with all the #pbifamily still in Copenhagen! 💛 pic.twitter.com/wY4wgxVTuL

— Nicky van Vroenhoven (@NickyvV) September 18, 2021

 

Slides and more...

The conference will send out all the slides, but if you can't wait for mine, you can find them at my GitHub page, or directly download the slides as PDF here.

During the conference, several people asked if the information from my session was also available on my blog somewhere, which actually is not (yet 😀).

So with renewed energy from the conference, I'm announcing my next topic here:

A blog series on Query Folding!

Keep an eye out for the first part in the next weeks!

List All Functions in Power Query with #shared

I decided to dedicate this short blog post on how to make better use of Power Query and all the M functions inside. Let's see how we can do that.

Thank you

But before I dive into my blog post, I'd like to thank the #pbifamily for looking out for me!

Thanks for all the help yesterday, wonderful #pbifamily, I 💛 you!

Short update: I started a blog on a #PowerQuery trick, which should be finished later today, yay 😁

If motivation is letting YOU down at the moment, have a read through this thread 👇, and know you're not alone! https://t.co/n3nhNVyD01

— Nicky van Vroenhoven (@NickyvV) November 3, 2020

#shared

In addition to what the title suggests, you can not only show all the functions, but all tables, records, (custom) functions and query (results) inside Power Query.

You can start using it by:

• Creating a new blank query and either:
• type in "= #shared" in the formula bar and press Enter, or
• open the Advanced Editor and replace everything with "#shared" and press Done
• Click on Into Table in the top left and you're done!

This will give you a list of functions (and query results, etc.) available in M.

You can search through the list of Names and e.g. see what functions you can use with week:

Opening e.g. Date.IsInNextWeek opens up the details for this function, showing you a description, the parameter used and the returned value.

You can even test it right there.

Watch it in action

If you like a more visual explanation, have a look at a recording of my Query Folding session at the dataMinds User Group earlier this year. The part about #shared starts at 52:40:

During the research googling for this blog post, I also found another thing you can do with #shared:

• The Environment concept in M for Power Query and Power BI Desktop, Part 3 by Lars Schreiber (@SchreiberLars | b) and Imke Feldmann (@TheBIccountant | b)
• Curious on how to start an embedded YouTube video on a specific time? Have a look at this blog post, you'll have to use "?start=" instead of "t=" for a regular URL

Power Query and The Curious Case of Keep Top Rows

The Power Query Editor in Power BI has many transformations that can be set from the UI. Keep Top Rows is one of them. I'd like to take you on my journey through this curious case I ran into a couple of weeks back when preparing for my session on Query Folding in Power BI.

Transformations in Power Query

Problem

I was preparing my demo's for my session at dataMinds Connect on Query Folding in Power BI (slides). If you're not familiar with query folding: transformations in Power Query are transformed into the native (data source) language and executed at the source for better performance. For a more thorough overview have a look at the post How Query Folding Works from Matt Allington (B | @ExceleratorBI).

If we look at SQL Server, some transformations can be folded (like Sorting, a simple Group By and Keep Top Rows), others cannot (merging two columns from the UI, using the Buffer() M-function and Keep Top Rows). See what I did there? :) Keep Top Rows on its own is a foldable transformation, it is (obviously) transformed into the TOP operator in SQL.

However, when using Keep Top Rows in combination with some other transformations it (looks like it) is not folding anymore. Your best chance is to use the View Native Query option in Power Query SQL Server Profiler to check if the query is folding or not. More on that later :)

A bit of contemplation: I do think the real use case of the Keep Top Rows transformation might be mostly in development environments, where you can limit the number of rows to import for example. I still think the findings were rather odd and worth sharing. So, let's have a look at how it works.

Setup

I used import mode for the Fact.Movement table from the WideWorldImportersDW database. I then added the Keep Top (100) Rows transformation in the Power Query Editor.

Keep Top Rows

As you can see it folds to SQL and all is good for now.

View Native Query with TOP 100

Adding transformations

Let's add a transformation on the Quantity column and apply an absolute value to it.

Add Absolute value of Quantity

So now the option to View Native Query is gone. Let's see if Power Query is speaking the truth (spoiler: No) 😀

I started SQL Server Profiler and captured the queries running against the database after I Close and Apply my changes. As you can see, now both the TOP operator and ABS function are folded.

SQL Server Profiler: TOP 100 and ABS

Now let's try something different and add a filter on the Date Key. Something strange has happened, because I already filtered the first 100 rows, so only 2 dates remained after that. But when I click on the Calculated Absolute Value query step I suddenly have a lot more dates that shouldn't be there in my opinion.

Filter on Date Key

Let's add a field from the Customer dimension this time and see what happens.

Expand Customer Dimension

The query for Fact.Movement is still the same, so the TOP operator and ABS function are still folding. And this is what is sent to the database for the Customer dimension:

SQL Server Profiler: Customer Dimension with WHERE clause

The Curious Case

Now watch closely to the where clause: so it actually already filters out the Customer Key based on the steps performed earlier in the Query Editor. But this Customer dimension is a separate query that returns 1 row, which will be joined to the other query inside Power Query.

Back to that WHERE clause: that Customer Key = 0 is valid for my (broken) Calculated Absolute Value query step, not for the Kept First 100 Rows as you see in the picture above. So I actually get the wrong a different result back into Power Query than I would expect.

I see you're thinking: "But you didn't add an explicit order, right?" So the top 100 is a little arbitrary. That's true, so let's add that order by.

Add Sorted Rows

This is a slightly better result in my opinion. Because now the Power Query steps are in sync with each other. The issue with View Native Query still remains the same by the way. It stops working at the Calculated Absolute Value query step, but the query send to the database is the following, so including the ABS function:

View Native Query after Sort

Recap

I showed several variations in the Power Query Editor of using Keep Top Rows and showed that adding subsequent steps brakes the View Native Query option in the editor, although query folding sometimes does take place. And adding an order to your query before applying a TOP operator is always a good idea, not only in SQL.

The steps I used might not all be best practices when creating a data model in Power BI. In case of the join to the customer dimension for example, it might be better to model it as a separate dimension, because STAR SCHEMA ALL THE THINGS. But I used those steps to highlight the problem.

Trying to make the STAR SCHEMA ALL THE THINGS into a meme. https://t.co/D7n6EfwuIS

— Koen Verbeeck (@Ko_Ver) October 24, 2019

I already mentioned this bug feature to Patrick and Adam (members of the Power BI CAT team) from Guy in a Cube (B | @GuyInACube) when I was at the Precon at Data Saturday Holland. Patrick LeBlanc (B | @patrickdba) also hadn't seen this before and hopeully will take it up with one of his team members. I thought I'd make a write up for him to easily reproduce it.

Speaking at dataMinds Connect in Belgium

I already posted a short update on LinkedIn, but I now finally also had the time to update my blog.

Query Folding in Power BI at dataMinds Connect

I had the honor of doing a session at the Newcomer track at dataMinds Connect. This was a special track for people like me that have (almost) no conference speaking experience. The conference assigned buddies to these newcomers. For that, I'd like to thank Koen Verbeeck (B | @Ko_Ver) for helping me enormously with feedback to and try-outs of my presentation.
My submission to this conference was actually the first I did earlier this year, after that I also submitted to the other places like SQL Saturday Prague. But this was actually my first technical session with lot's of demo's.

Slides on Github

The slides were already shared with the conference organizers, but are also available via my Speaking page. I recently made some changes to the way I share them because I moved all my talks and slides to my Github. I still have some things and links to add there, but the basics are there.

I also did some calculations on the feedback I received and I was pretty happy with the average of 3.9!

Feedback scores

Half marathon Eindhoven

Like I mentioned in my session at dataMinds Connect I also participated in the half marathon in Eindhoven on Sunday October 12.
As it was relatively warm with 24 degrees Celcius, I had to shelve my plans of running a PR fairly quickly after 5K. A lot of people also were struck by the warmth and humidity that day

Ouch, that was a heavy one.. hot and tough. No PR, but I DID finish it 👊 #runhappy pic.twitter.com/0cyPXiY6GU

— Nicky van Vroenhoven (@NickyvV) October 13, 2019

Power Platform World Tour in Dublin

The next stop is the Power Platform World Tour in Dublin where I'm presenting my Tips & Tricks of our Power BI implementation! I'm also doing one of the "Show & Tell" sessions at the Power Platform World Tour Meetup on the evening of October 30, organised by Ben Watt (B | @benrebooted).
I can also offer a $100 discount to all members who are part of their local Power Platform User Group community with the following code: 2019PPWT100DUB

NvV

Bits of BI – June 6

In accordance with Thomas LaRock I decided to also start my round-up of news from around the block of SQL Server, Technology or anything vaguely related to BI. And I might include some other things as well if I feel like it.

My plan is to do a weekly round-up, let’s see how that works out.

So here’s the first edition of Bits of BI!

• Query Folding in Power BI
• Power BI Desktop June Feature Summary
• Subscribe others to email subscriptions in Power BI
• Microsoft + Github = Empowering Developers
• Announcing: The SQL Server Execution Plan Reference

I hope you found this edition of Bits of BI useful, feel free to share it with others!

@NickyvV

My Data & BI Summit Recap

I finally came around to writing a wrap-up for the Data & BI Summit in Dublin last month.
No fancy pictures or colors, just a to-the-point summary.

New features

• Incremental refresh – Premium (included in May update as preview feature)
• Drillthrough with current filters and parameters (included in May update)
• Common Data Service For Analytics (preview live)
• Theme file additions coming soon:
• Theme export
• Background added to themes
• Table/matrix headers
• Persistent filters (per user) in the service
• Buttons to trigger action (included in May update)
• Q&A and linguistics
• App workspaces without Office365 group

Modelling

• Optimize your model (size)
• Make use of Query folding in Power Query
• Applies to Import mode only
• Filter first, Transform later
• Use supported transformations first, non-folding steps at the end
• Writing a SQL query in Get Data prevents folding
• Create a blank query with #shared to show a document library of all M functions (Blog)

Visualization

• Choose a theme
• Report theme generator at PowerBI.tips
• Color Theme Creator at Aka.ms/pbitheme
• Outlining
• Canva.com
• PowerPoint
• PowerBI.tips
• Storytelling, with:
• Custom visuals (e.g. Timeline Storyteller)
• Bookmarks
• Tooltip pages
• PowerApps visual
• Drillthrough
• Syncing slicers

Governance

• Use App Workspaces for DTAP
• DEV_Finance
• UAT_Finance
• Finance
• Use Onedrive for Business for versioning your pbix-files, with the same folders as the App Workspace
• Use the API’s with PowerShell to copy resources between workspaces
• Chriss Webb (@cwebb_bi) wrote a successful script in response to a video from Adam Saxton (@GuyInACube)

Best practices and notes

Visualization | Model

• Driving table for measures
• Hide unused columns
• Totals in matrix and tables insensitive to row filters
• Uniform naming of measures and calculations
• Sync slicers
• Add descriptions where possible
• Make sure filters are not hardcoded

Governance

• Add an about page with last refresh date time and description of Model
• Version the Model
• Schedule datasets using Admin account for one place to monitor failure notification emails
• Use one drive as source control
• Golden dataset

Further reading

• Power BI Whitepapers
• PowerBI.tips
• Power BI User Voice

@NickyvV