Converting Multiple Items into One Row in Power Query

I was working with some external data where I had a list of Australia postal codes, the challenge is that there are multiple suburbs that are part of one postal code.

Below I am going to show you how I took the multiple suburb names and put them into a single row, which would then allow me to have a single postal code per row. Which would then allow me to join this to my table using a relationship in my data model.

This is what the table looked like before the transformation 

 

This is what the table will look like once I have completed the steps below 

 

How I did it  

·         I first selected my column called postcode, and then in the Transform ribbon clicked on Group By  

o     

·         I then configured the Group By as shown below.  

o      

o    What I am doing here is to group by the postcode, and then putting the remaining rows effectively into a table by using the “All Rows”  

o    This resulted in a single row per postcode  

§    

·         I then went into the Add Column ribbon and clicked on Custom Column  

o      

·         I then put in the following code into my Column name “Suburbs” 

  • NOTE: Even though I cannot see the column called [locality] any more in the Available columns, I can still use it in my Custom Column.
  • This resulted in the following in my table
  • I then clicked on Expand on the Suburbs column and selected “Extract Values”
  • I then selected the delimiter to be a comma for the list of values
  • I could then see my new Suburbs column with the extracted values being comma delimited
  • I then removed my unwanted column called AllData and renamed the column headers to get my final result.

Conclusion 

I have shown once again how versatile Power Query is and also that very often things can be done via the GUI which makes it intuitive and easy to shape your data.

If there are any questions or comments, please leave them in the section below.

Thanks once again for reading and I hope that this will be useful to you at some point!  

Multiple conditions for a conditional column in Power Query

I am often working on datasets where there is more than one condition for a conditional column.

And whilst the GUI based Conditional column is really good, it currently does not have the capability for multiple conditions.

In this blog post below, I will demonstrate how to achieve this.

In my example below, I have a table that has got Bike Brands and Types.

I now want to create a rating based on both the Brand and Type.

I do this by creating a Custom Column

The way the multiple conditions work is based on the following pattern:

if [Column Name1] = “Condition” and [Column Name 2] = “Condition” then “Result”

else if [Column Name1] = “Condition2” and [Column Name 2] = “Condition2” then “Result2”

else if [Column Name1] = “Condition3” and [Column Name 2] = “Condition3” then “Result3”

else “Unknown Result”

This is shown with my working example below.

Which results in my table now having a new column called Rating which has the multiple conditions for my conditional column.

Another thing to note is that I could also do a range between two values which is essentially multiple conditions for a conditional column.

What I had to first do was to change the Amount column from Text to Whole Number, so that my conditions would work. After which I then used the following conditions

Which resulted in getting the banding that I was after.

And finally, (the last one I promise!), is where I can use the flexibility within Power Query to convert the Amount value on the fly from a Text value to a Number value for my conditional column.

This will allow me to keep my column in my table as a text value.

Conclusion

I hope that you have found this blog post useful and as always if you have any suggestions or comments please leave them in the section below.

Thanks for reading!

Power Query – A function to remove spaces within Text values

UPDATE: 19 Nov 2018

With the recent announcement of dataflows in the Power BI Service, I see more people will be looking to better understand and leverage dataflows by using the M language which is available in Power BI Desktop, Power Apps and Microsoft Flow.

I had a great response to this blog post, and both Ted and Daniil had a much easier solution to remove spaces from the data in a column. I am not sure why this originally did not work for me, but I am always happy to learn from others. As well as find an easier way to achieve the same outcome.

The solution is all that you need to do, is to right click your column and click Replace Values, as you can see below I am searching for the space and replacing with no space

 

 

 

BELOW IS THE ORIGINAL BLOG POST

The Power Query function below will remove any spaces that I have in a text field.

I got the original Power Query function from Ken Puls blog post Clean WhiteSpace in PowerQuery which he does a great job of removing leading, trailing or multiple spaces within text.

My requirement was to remove any spaces within the text too.

I created a Blank Query in the Power Query Editor and named it fx_ReplaceSpaces

Below is the code that I actually used.

And here is what it looks like with some sample data

And this is the output, where I wanted all spaces removed

There might be a more elegant way to achieve this, so if anyone has got any suggestions please let me know, I will be happy to test and update this blog post.

Review of new Features coming to Power BI by Oct 2018 – From Business Applications Summit

I was fortunate to attend my first ever Business Applications Summit in Seattle.

I had the pleasure of sharing an Airbnb with Matt Allington, Phil Seamark and Miguel Escobar, it was great to spend time with these Power BI Legends. I also did meet a lot of people and got to chat with people in the Microsoft Power BI team, which was something I will always remember.

I also was fortunate to present 2 sessions (Unfortunately Reza and Leila could not make it) at the Business Applications Summit, both sessions went well, and the feedback was positive.

Ok, so enough about me, let me get onto ALL the new features that are planned to be coming to Power BI until October 2018.

One thing I can say is that I am SUPER excited with the new proposed features that are coming, it is most definitely going to make Power BI the go to BI tool going forward.

Along with this it is also growing up, and by that, I mean more enterprise features are coming, which means that it soon will be able to be implemented into a smaller organisation or a fortune 500 company.

Personally, I cannot wait to learn all the new features and start to implement them at customers. One caveat is I am certain that some features might take a bit longer to get into the service or could possibly change.

NOTE: This might be a bit of a longer post, so buckle up, here is the link to where I got a lot of the information: Overview of Business Intelligence October ’18 release

Other Features not mentioned in any of the notes

Below are some of the other features that I did not find in the release notes but there were some demo’s or pictures.

Print to PDF

As you can see above, coming to Power BI will be the ability to print to PDF which will look exactly like your Power BI report.

Display Folders – Multiple selection settings

As you can see above there is the ability to be able to set multiple measures or columns into a Display Folder.

Not only that but you will also be able to complete the settings over multiple columns.

And something not in this picture is the capability to see multiple data source views if you have hundreds of tables to make the data modelling experience easier.

Python Support

As you can see above there is Python Support coming to Power BI!

Personal Bookmarks

As shown above, there will be personal bookmarks coming to Power BI

Power BI Desktop

Below are all the Power BI Desktop upcoming features

Ad-Hoc Data Exploration

What this will be a user who does not have edit access to a report will be able to look at a chart on a different axis or change the chart type which could be more meaningful to the user.

It will be used with an option to choose a report and select “Explore from here”

Aggregations

This is a big change for Power BI where there the underlying data is a really large dataset that is stored in Spark or a SQL Server database. When connecting with DirectQuery you will be able to define aggregations which will cache just the aggregated data into memory into your model.

This will allow to have a dual mode so that if the query can be answered by the aggregated cache that will be used, and if not, it will then query the underlying DirectQuery source.

Composite Models (Available Now)

What Composite Models allow you to do, is to allow you to have data in one Power BI Desktop file where you are getting data from DirectQuery and imported data sources.

This is an amazing feature and I know something that a lot of people have been asking for.

With this you also now by default will have all relationships set to Many:Many.

As with the details it is always suggested to ensure that your DirectQuery source has been tuned and has the capacity to be able to answer the queries from your users to ensure that the users get the super-fast reporting experience.

Currently the Composite Models do not support LiveConnection sources which only relates to SQL Server Analysis Services (SSAS) Multi-dimensional or Tabular.

Copy data from table and matrix visuals

Coming to Power BI Desktop and the Power BI Service once implemented will be the ability to be to copy data from a matrix or table into another application.

Custom Fonts

You will soon be able to use any fonts that you want in your Power BI reports. All that will be required to work is that the same font will need to be installed on their computer in order for them to see it. If they do not have it installed it will fall back to the default font.

Expand / Collapse in Matrix Visual

As shown below you can see the upcoming option to expand or collapse rows in a matrix visual. There also was the indication that they want to bring more pivot table features from Excel to Power BI.

Expression-based formatting

What Expression based formatting is, is where by using DAX you will be able to format almost anything in your Power BI report. The potential is to use expression-based formatting for the following below and possibly more that I cannot think of

  • Titles of Visuals
  • Line widths of visuals
  • KPIs based colours

From what I did see, there will be an fx button next to almost everything in the visual properties and elsewhere.

Q&A Telemetry Pipeline

This will allow access to the Q&A telemetry to see what the users are using Q&A for, which will allow you to further customize your Q&A Linguistic settings. The data will first be scrubbed for PII data.

Dashboard and Report wallpapers

Coming to both the Power BI Dashboards and reports will be the ability to use wall papers to cover the grey area behind your reports.

Show measures as clickable URLS

As you can see above you will be able to create a link, with a measure so that this can dynamically link to a Power BI Report to any other application which you can access via a URL

Theming over report level and visual container styles

There will be a theming update coming to both report level and visual containers in Power BI. From what I understood it would be similar to the theming that you can currently do in Power Point.

New Power Query Capabilities

There are a whole host of Power Query Updates as detailed below.

Intellisense support for the M formula language

Intellisense will be coming to the advanced editor in Power Query. This is something that I know a lot of people have been asking for. Not only that by Power Query will be coming to Microsoft Flow also.

Smart Data Prep

There are smart data preparation coming to Power Query, with the following initial features below.

  • Data extraction from semi-structured sources like PDF files.
    • This is something a lot of people have been asking for and I have seen it in action and it is awesome, where it can take data out of tables in a PDF and extract it into Power Query.
  • HTML pages.
    • A smarter experience to understand what details you want from an HTML Page.
  • Fuzzy-matching algorithms to rationalize and normalize data based on similarity patterns.
    • This is where it will try and match data based on your columns, to try and guess what the value should be when say the data is miss spelled.
  • Data profiling capabilities.
    • As you can see from the above image, there will be data profiling which will enable you to have a look and see if the data is as expected.
    • An example is if you know that your Customer Number should only be 5 characters long, with the data profiling you will be able to see if it is meeting your criteria.

Power Query Community Website

As with Power BI, there will be a Power Query Website launching later this year.

Certified Custom Connectors

There will be certified custom connectors which will be available to be plugged into Power Query. As part of this process the custom connectors will be certified Currently there are no additional details on how this process will be completed, but no doubt it will explained as time goes on.

Power BI Service

Below are all the updates to the Power BI Service.

Power BI Home

Power BI Home is a new place for users to start their Power BI Journey with the following sections.

  • The Top section will contain the users most important dashboards and reports.
  • The second section will contain their favourite items, as well as most frequently accessed reports and dashboards.
  • Whilst the bottom section will include learning resources.
  • And on the top right-hand side will be a global search where you will be able to search for any item that you have access to in the Power BI Service.

Paginated / SQL Server Reporting Services Reports

As you can see above SSRS or Paginated reports will be coming to the Power BI Service. Not only that but it will also print them out pixel perfect.

Workspaces with Azure AD groups

As you can see above, App Workspaces will be using Azure AD Groups and not dependant on Office 365 groups. You will still be able to add permissions from Office 365 groups, but it will no longer be dependent on Office 365

Data Flows – (Formerly called CDS-A, then Data Pools, now Data Flows)

It started out being called CDS-A, then Data Pools, and now the final name according to the great people in the Microsoft Power BI Team is Data Flows. As previously described this is where you will be able to use Power Query Online to ingest data from anywhere and store them in Entities.

All the data will be stored in an Azure Data Lake Gen2, Pro license getting 10GB per user and Premium will get 100TB per P1 node), which will give you the additional capabilities to be able to let the data scientists in your organisation access the data directly from the Azure Data Lake. You will also be able to bring your own storage within your existing Azure investments.

I think that having the Data Flow will enable organisations to be able to have a single source of truth for their data assets, that can then be leveraged by the entire organisation.

What I do know from attending Miguel Llopis session is that the same runtime that is running in the Power BI Desktop will be running in the Power Query Online in the Data Flows. So that will mean you can use Power Query within Power BI Desktop to get the data in the shape that you want. Then you can go into the Advanced Editor and Copy and paste the data into Power Query Online.

Below is a rough overview of what it looks like

Data Flows Refresh

Not only will you be able to bring data in with data flows, you will also be able to refresh data with incremental refresh which will be a Power BI Premium Feature

Admin APIs

Admin APIs have come to the Power BI Service, which will allow an Admin of the tenant to be able to discover all the artefacts in his Power BI tenant.

This is great for large implementations, because up until now you had to have access to the App Workspace in order to be able to view the data. Now by being an Admin, you will see everything, which is the standard Admin capabilities.

Additional report URL parameter support

Additional report URL parameters will include filters for Date columns, new operators ” < , > , <= , >=” and multiple field values

Commenting in Dashboards and Reports

As shown above there will be the ability to comment on Dashboards and reports.

Not only that but you can include people using the @ which will then send them a notification.

Along with this you can also add comments to a specific visual, which will give great context to comments

Dataset metadata translations

If you have defined translations in the dataset or Analysis Services model, the user will see it in the locale.

Filters for Report Sharing

You will be able to share reports to users with the current filters and slicers in place for when they view the report.

Historical Capacity Metrics

If you have got Power BI Premium capacity, there will be a historical view which will allow you to see what affects performance or refreshes and queries. And see which datasets consume the most memory and make changes or plan accordingly.

Multi-Geo for Compliance

This will be where data must reside in a certain country, where a company operates around the globe. And will ensure that the data can be located in any of the Azure Data Centres, even though the initial Power BI tenant might be located elsewhere.

Not only that but in a future release this can also be used for performance by having the Power BI Premium located closer to the users.

Azure Analysis Services / Analysis Services Tabular

Below are some of the new features coming to SSAS in Azure or On-Premise in Tabular

XMLA endpoint for third-party client connectivity and lifecycle management

By opening the XMLA endpoint for Power BI, any current tool that can connect to SSAS via XMLA will also work with Power BI Desktop. Which means that some of the following tools would work immediately once the XMLA is opened.

  • SQL Server Management Studio
  • BISM
  • Excel

Not only that but you will also be able to use the TOM and TMSL in order to manage and modify configuration settings or items within your Power BI Model.

Application Lifecycle Management

As you can see above there will be the capability to be able to have full Application Lifecycle Management ALM in Power BI Desktop, Azure Analysis Services. This is a great step forward because it gives you the following capabilities

  • Source Control
  • Deployment of specific items
  • Deployments to Dev, Test and Prod
  • Potential to share parts of the data model.

Analysis Services vNext

As shown above this was Christian Wade from the Analysis Services team showing the following potential features coming to Power BI and Analysis Services.

  • Calculation Groups
  • Many-Many relationships
  • Resource Governance
    • Is Available in MDX
    • Query Memory Limitations
    • Rowset Serialization Limits
  • Data Connectivity
    • New Power Query Enterprise Data sources for Spark, Amazon RedShift, IBM DB2, Google BigQuery and Azure KustoDB

On-Premise Data Gateway Updates


There have been a lot of advancements in the On-Premise Data Gateway, with the latest release including Support for Custom Data Connectors in the Enterprise version of the On-Premise Data Gateway.

Below is a list of features coming to the On-Premise Data Gateway

  • Gateway multi-geo support for Power BI Premium
    • With the release of multi Geo Support for Power BI, this will also be enabled in the On-Premise Data Gateway
  • Guarantee high availability of gateways via clustering
    • Better support and visibility for Gateways in a Cluster
  • Improved support for Single Sign On and SAML
  • Improved data source settings experience
    • Here there will be the ability to skip the testing of the connection.
    • Rename data sources.
    • Create multiple data sources with different credentials
  • Tenant level administration of on-premises data gateway
    • This will allow the ability for tenant administrators to manage All On-Premise Data Gateways via the API or GUI
  • Basic traffic load balancing in the on-premises data gateway
    • This will start off with a basic setting to split the traffic requests between Gateways.

Power Query Pattern – Adding Spaces in Text within your data with Camel Case

In this week’s blog post, I created this Power Query Pattern, which I created to add in spaces for CamelCase text within a column.

To get this to work for you, all that you do is need to make one change to the code.

Below is what the data looked like

Then I created the following Power Query Pattern below.

 

  • To use this pattern below are the following changes that you will need to make it work in your Power Query Editor
  • Line 1
    • This is my step name
  • Line 2
    • This is where I am using the Table.TransformColumns
    • NOTE: Even though this does appear to only be used for transforming columns, it works for data within a column.
  • Line 3
    • This is referring to the previous step name, which is returning the table contents
  • Line 4
    • This is my column name where I want to add in the spaces. As with my image above the column was named “Operation”
    • NOTE: This is the only part of the pattern that needs to be changed.
  • Line 5

What I did to get this to work is I was in my Power Query Editor Window

I then clicked on Advanced Editor in the Home ribbon

I then added this step into my code as shown below.

I then clicked on Done

I went back to my column and I could now see the data with the spaces after each capital word

Conclusion

As you can see I have demonstrated how Powerful Power Query (see the multiple use of Power!) is to get the data in the shape that you require.

If you have any suggestions or comments please let me know.

Power Query – Renaming Multiple Columns

I was working on a dataset where I wanted to change multiple column names using one step and not having to change them manually. Since there were over 30 columns this would be really time consuming.

Below I detail how to complete this in the Power Query Editor, which will replace all the column names for me in one step.

This once again shows how powerful the Power Query Editor can be for ETL and automation tasks!

  • This is what the Original Column names looked like
  • To get this working, I had to go into the Advanced Editor and manually add a step.
  • Here is the Syntax that I added with my Step Name

    #”Rename Column Names” = Table.TransformColumnNames(#”Changed Type”, (columnName as text) as text => Text.Replace(columnName, “Sales – “, “”))

  • As you can see above my previous step name was called #”Changed Type”
  • Then the only other change I had to make was for the Text.Replace and what I was searching for and what I wanted to replace it with.
    • With this example I was looking for “Sales – “, “”
      • As you can see I searched for Sales – and replaced with “” (which is blank).
  • And this is what it looked like after manually adding in the above step.

As I have shown above a quick and easy way to rename multiple columns at once.

You can find the sample file here: Renaming Multiple Columns.pbix

As with every blog post if there are any comments or suggestions please leave them in the section below.

Power BI – Turning off (Disabling) Native Database Queries

In this quick blog post I am going to show how to turn off or disable having the dialog
box which pops
up when using Native Database Queries.

This can sometimes be an extra step on something that you have to action when refreshing your data either in the Query Editor or in Power BI Desktop.

NOTE: I was often prompted with the following below for EACH native database query.

As you can see below this is typically what would happen if I ran a native database query

  • I would first get the yellow car asking for permissions to run the native database query.
  • Then once I clicked Edit Permission I then got the following Window, showing me the query that is going to be run.
  • Once I click Run it would then run the query.
  • So this can be time consuming especially if I had multiple queries.

How to turn off or disable the Native Database Queries

In the steps below I will show how to easily turn (Disable) the native database queries

NOTE: I could complete the steps below from either being in the Query Editor or within Power BI Desktop.

  • Click on File and then Options and settings and then Options
  • Then under GLOBAL I clicked on Security
  • Then in the Security settings at the top it has got a section which says Native Database Queries you will see an option.
    • And there is a tick
      next to “Require user approval for new native database queries
    • I removed the tick.
  • Then click
    Ok.

Now I went back and ran my native database query and it simply
ran with no requests to Edit Permissions or to Run the query.

Conclusion

I have shown how to disable the native database queries which not only helps when I am developing but also assists me when my Power BI Desktop file is refreshing.

Power Query – How to get same row number for x number of rows

I was answering a question on the Power BI Community Forum where a user was looking to get some data from an Excel spreadsheet into Power BI. And in order for me to complete it, I wanted to get the same row number for x number of rows.

Example

  • I had some sample data where the same data was repeated every 7 rows
  • What I wanted is that for every 7 rows the Index number to be the same.

(more…)

Using Power BI to ensure Daylight Savings Dates and Times change dynamically

I was helping on the Power BI Community and it struck me that there currently was not a way to easily manage Dates and times for Daylight savings. In this blog post below, I will show how I achieved this by using parameters and little bit of M magic (Directly Editing the M Code).

As part of completing this solution I also included the UTC Offset, which I did blog about previously Power BI – Did you know ALL Power BI Services Servers are in UTC? Now how to handle it for Dates & Times which will ensure when the Power BI Desktop file is uploaded and refreshed within the Power BI Service it displays the correct Date and Times.

(more…)

BI-RoundUp – Power BI (June Desktop Update – Data Insights Summit Details – Power BI Embedded Pricing & Details – Power BI Premium Generally Available – Power BI Report Server Generally Available)

I was fairly certain that there were going to be a whole host of updates and additions to Power BI, and I can say that I am not disappointed. There is a whole host of information below, where I will endeavour to try and get it all to you, so this might be a bit long to read, but it will be worth it.

(more…)