Power Query in Power BI Desktop!!
What is Power Query?
Power Query is all about cleaning and analyzing the data. It is an ETL Service tool (Extract, Transform and Load).
It allows you to import data from various sources which we call Extraction of the data and then we Transform (cleaning of the data) and finally Load the data.
Text Function in Power Query!!
Merge
In Excel, we use concatenate function to merge different columns. But here in Power BI, we use the merge function as below:
Extract the Data > Transform > In Power Query Editor select the column in the order you want to merge > Transform > Merge Data > Select space and provide the name of the new column > Outcome is the merged data in the new column > Close and apply changes
Since we are working in Power BI, we can only apply the changes we are making. If we make any change in the main data source in the power query it will get changed.
Note: In Power BI there is a feature where if you want to refresh any data, you can do that automatically. That can be seen when using Power BI Service. When we publish from Power BI Desktop to Power BI Service it gets refreshed automatically. We can even set a time (every day/every week etc).
Split and Trim
Split in Power BI Query is the opposite of merging the data. If you want to separate the text which is mentioned with delimiters in a separate column, you can use Split Function. For eg (Pune, and Maharashtra, are both in the same column and we want a separate column for City and State then we can use Split)
Trim is used when you want to remove any unwanted space in the column.
SPLIT:
Extract Data > Transform > Split > Delimeters > Separate column as Outcome
TRIM:
Extract Data > Select the Columns > Transform > Format > Trim
Upper, Lower and Proper
This function is used when you want to clean the data. Sometimes the data are mentioned improperly some are in upper case and some are in lower case for the same name of BikrAm. So to clean this type of error we can use the Upper, Lower or Proper function in Power Query.
Extract Data > Select Column/s > Transform > Add Column > Format > Choose the function (Upper/Lower/Proper)
For Proper you need to choose Capitalize Each Word.
Add Predix and Suffix
If you want to add any prefix before the character or suffix after the character you can use this function. If there is a column name Age then the data type will be showing as numbers, the moment you add any suffix say 13 Years, then you will notice the data type has changed to Text in Power BI.
Extract Data > Transform > Add Column > Format > Add Suffix > Value > Outcome as suffix will be added.
The same step follows for Prefix in place of Suffix it will be Prefix.
Extract Text in Delimiters
If you want a text that is in between any delimiters and you want that text in a separate column then you can use this function. Eg Saransh#21@Delhi#1658, so if I want to get say only 21 which is between # and @, we can use this function.
Extract Data > Transform > Add Column > Extract > Text Before / Text After / Text Between > Outcome will be in a separate column
That’s it for today’s blog.
If this post was helpful, please follow and click the 💚 button below to show your support.