Home Tags Posts tagged with "Power Query"

Power Query

REPLACE ALL NUMERIC VALUES IN MULTIPLE COLUMNS USING #POWERQUERY

A quick post more for my future reference. Hoping it helps some one out there!

While I was working on a Power BI report with Commonwealth Games dataset, I came across an issue where I had to replace all numeric values with a text value. There are some very useful blogs posts about replacing multiple values in Power Query. With my issue, I wanted to replace only numeric values. Instead of replacing each column, I tried to replace all!
My data looked like below:

I looked upon all available Replace functions of Text, Lists and Tables. As Imke mentioned on her blog, I decided to go for List.ReplaceMatchingItems. As I wanted to replace only numeric values, I used Value.Is(Value.FromText(_), type number)  to check the Type of value for each row. I replaced value only when my condition returns true. So I combined all required columns into a single column and used Text.ToList function to convert to a List. The condition I used for Type checking works great. But, when I converted the combined columns to Text, then to a List; each character in Text value converted into List as a different List Item. E.g. 12 would convert to {{1},{2}}. When I replace each by checking the data type, I will be replacing 1 and 2 individually, which will cause duplication in the result, like below:

 

I have to admit; I searched so much to figure out a way to covert Text value to a List using a delimiter.I felt silly after I looked at one my earlier post where I was using Text.Split() function. After a good number of failed attempts, I used Text.Split() function and then I used that List for replacements. And my condition Value.Is(Value.FromText(_), type number) works great.

 

Here is the script:

let
    Source = Web.Page(Web.Contents("https://en.wikipedia.org/wiki/2018_Commonwealth_Games")),
    Data4 = Source{4}[Data],
    #"Replaced Value" = Table.ReplaceValue(Data4,"#(cr)#(lf)"," -",Replacer.ReplaceText,{"April", "April2", "4 #(lf)Wed", "5 #(lf)Thu", "6 #(lf)Fri", "7 #(lf)Sat", "8 #(lf)Sun", "9 #(lf)Mon", "10 #(lf)Tue", "11 #(lf)Wed", "12 #(lf)Thu", "13 #(lf)Fri", "14 #(lf)Sat", "15 #(lf)Sun", "Events"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value","#(lf)","",Replacer.ReplaceText,{"April", "April2", "4 #(lf)Wed", "5 #(lf)Thu", "6 #(lf)Fri", "7 #(lf)Sat", "8 #(lf)Sun", "9 #(lf)Mon", "10 #(lf)Tue", "11 #(lf)Wed", "12 #(lf)Thu", "13 #(lf)Fri", "14 #(lf)Sat", "15 #(lf)Sun", "Events"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Replaced Value2", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"April_1", "Type"}}),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Renamed Columns",1),
    #"Filtered Rows" = Table.SelectRows(#"Removed Bottom Rows", each ([Type] <> null)),
    #"Replaced Value1" = Table.ReplaceValue(#"Filtered Rows", each {"4 -Wed", "5 -Thu", "6 -Fri", "7 -Sat", "8 -Sun", "9 -Mon", "10 -Tue", "11 -Wed", "12 -Thu", "13 -Fri", "14 -Sat", "15 -Sun"} ,each if Value.Is(Value.FromText(_), type number) = true then "_" else "_",Replacer.ReplaceText,{"4 -Wed", "5 -Thu", "6 -Fri", "7 -Sat", "8 -Sun", "9 -Mon", "10 -Tue", "11 -Wed", "12 -Thu", "13 -Fri", "14 -Sat", "15 -Sun"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value1", "Custom", each Value.Is(Value.FromText([#"15 -Sun"]), type number)),
    ColumnNames = List.Select(Table.ColumnNames(#"Filtered Rows"), each Text.Contains(_,"-")= true),
    CombineColumns = Table.CombineColumns(#"Filtered Rows",ColumnNames ,Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Added Custom1" = Table.AddColumn(CombineColumns, "Custom", each Text.Split([Merged],",")),
  #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.1", each List.Transform([Custom], each if Value.Is(Value.FromText(_), type number) = true then "GME" else _)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"April", "Type", "Events", "Custom.1"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Other Columns", {"Custom.1", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom.1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), ColumnNames )
in
    #"Split Column by Delimiter"

This may not be worthy to be a blog post especially when there are already some good posts covering the similar topic, but I spent a fair bit of time on this so thought of writing a post!

Keep Smiling,

Prathy 🙂

0 comment
0 FacebookTwitterPinterestEmail

Recently, I worked on an interesting problem. Datasource I was working with was a SharePoint list, working with SharePoint lists always turns out to be a difficult task than I first assumed. On top of that, with this particular data source; data was coming as semicolon delimited text. Basically, there were many columns like Mile Stones and Sub Milestones, their related data and so on.

Initially, when I looked at it, I thought, I could just split columns in a jiffy; apparently, I didn’t think through it. To cut a long story short, I had to find a way to split multiple columns into rows at the same time so that I can visualise it in a Gantt chart format.

Split multiple columns into rows in #PowerQuery:

Here is an example dataset. My data looked something like below; the real data set has far too many columns and much more data in each cell.

Name Milestones Sub Milestones
Prathy m1;M2;M3;M4;M5 s1;s2;s3;s4;s5

If I split Milestones and Sub Milestones one column after other by using Splitter.SplitTextByDelimiter function, this is how my data will look like. There is no way I can find the which sub-milestones belongs to what.( Well, there must be away, but def, not an easy way)

So, instead of splitting each column, I Split each row using function Text.Split and added it as a new column. Text.Split returns a list. Later, I used all these lists to create a column using Table.FromColumns() function which returned a table for me. When I expand the table, I have all my columns split nicely the way I want.

Power Query Script:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCihKLMmoVNJRyjW09jWy9jW29jWx9jUFChQbWhcbWRcbWxebWBebKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Milestones = _t, SubMilestones = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Milestones", type text}, {"SubMilestones", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Milestones],";")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Split([SubMilestones],";")),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each Table.FromColumns({[Custom],[Custom.1]})),
    #"Expanded Custom.2" = Table.ExpandTableColumn(#"Added Custom2", "Custom.2", {"Column1", "Column2"}, {"Column1", "Column2"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom.2",{"Name", "Column1", "Column2"})
in
    #"Removed Other Columns"

I did this with multiple steps for better readability, but you can fit all statements in a single statement as well. I kind of feel like, there must be an inbuilt function which does this, but I haven’t discovered it yet, if you knew, please do let me know 😊

You can download the sample file here – https://1drv.ms/u/s!Avm7gbgZtlMlvjUCCvb981Ga_AVB

Keep smiling 😊

Prathy

 

 

 

 

 

5 comments
4 FacebookTwitterPinterestEmail

If you are the goto person for Power BI in your organisation, then I am sure at least once you may have been asked about Power BI users question, I get asked many times. Of course, I can go to Power BI Service, check each workspace and Power BI Apps and see who has access to what; not a productive way when I am working with many workspaces. Or as I blogged before, I can use Power BI API to get all users data, probably a better way when I am working with many workspaces. If I share content using Power BI Apps and given access using security group, It gets difficult to understand who has access to what, who is a member of what groups.

If you have enough rights, you can view users information and members of which groups in Office 365 portal, again not a very productive way.  However, by using Power BI and Graph API, we can create a report to show all users information. I will be able to see all users who have Power BI license. I can slice this data by Location, Team and many more. It’s not only Power BI; I can view all other licenses. For example, Power Apps and Microsoft Flow. Now I can easily see who has access and monitor users and license, If you want to know about Graph API and how to use it with Power BI, check out my earlier blog post – Access Microsoft Graph API using Power BI.

The Template:

By using Microsoft Graph API, I created a template, where you can see all users, their groups and their license details. You can download the template here – https://1drv.ms/u/s!Avm7gbgZtlMlqB12UF4WXFGzzgXk .

Using template:

When you open the Pbit file, you will see a pop up window with two parameters.

Enter these values Graph API URL = https://graph.microsoft.com/  and Graph API  Version =  beta. After a while, based on size, the template should get populated with data.  If you want to understand the logic behind the template, carry on reading …

10 comments
2 FacebookTwitterPinterestEmail

SharePoint is a killer; front end looks nice and beautiful. Once you start exploring behind the screens, that is when it shows the true colours. Whenever you view a list in the SharePoint mostly, you are looking at a SharePoint list View, so it does not show many underlined columns, not even columns with their real names. When you use SharePoint list as a source for a Power BI model, apparently it extracts all the information from the backend, which means you see far more columns than you expected and most of the times column names look obscure.

For example, look at following columns, how anyone can interpret s6hu meant to be column Jan-17

Original Name  Display Name
Title AI Number
cybf Aug-17
Notes1 Notes
s6hu Jan-17
a6ua Feb-17
dnxw Mar-17
uv0g Apr-17
yqkw May-17
ContentType Content Type

If you have faced this problem, want a quick fix, then just download this template. It should rename columns with display names wherever possible, or you may want to read rest of the post, you will know how I created the Template.

SharePoint List Power BI Template

Step 1:

First I created two parameters one for SharePoint URL and one for SharePoint list ID.

[ Description = "Sharepoint List URL" ] SPListURL = null meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=true];
[ Description = "Sharepoint List ID" ] ListID = null meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=true];

If you are not sure about getting List ID follow this URL or download this template – Get Sharepoint List ID’s. I used following M Code in the Get Sharepoint List ID’s template.

#"SharepointList IDs" = let
    Source = SharePoint.Tables(SPListURL, [ApiVersion = 15])
in
    Source;

Bingo, you get all lists from the given SharePoint site.

Step 2:

Once I know the URL and ID, I created another query with following M Code

 SPList = let
//SPListURL is the URL of sharepoint list
Source = SharePoint.Tables(SPListURL, [ApiVersion = 15]),
/* ListID is the ID of Sharepoint list */
SPList = Source{[Id=ListID]}[Items],
#"Keep only FieldValuesAsText column" = Table.SelectColumns(SPList,{"FieldValuesAsText"}),
RecordColumnNames= Record.FieldNames(#"Keep only FieldValuesAsText column"{0}[FieldValuesAsText]),
ExpandColumn= Table.ExpandRecordColumn(#"Keep only FieldValuesAsText column", "FieldValuesAsText",RecordColumnNames)
in
ExpandColumn;

I navigated to the list I want to import data from, chose FieldValuesAsText column only. FieldValuesAsText has text representation of all list data. Then I used function Record.FieldNames to get all values from Record. This makes it easy for future proofing. If any user changes columns in the future, Model will be clever enough to know the new column names. Then Expanded FieldValuesAsText column with the RecordColumnNames list.

Step 3:

I referenced the previous query and this time extracted all available column names using function Table.ColumnNames. Later I am going to use this query to join the query from Step 4.

 ListColumnNames = let
    Source = SPList,
    ColumnsNames= Table.ColumnNames(Source ),
    #"Converted to Table" = Table.FromList(ColumnsNames, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table";

Step 4:

I created another query with the same source as step 1 but in this query, I navigated to field ContentType and then Fields. Apparently, this is where SharePoint hiding all its secrets 🙂

 Title = let
    //SPListURL is the URL of sharepoint list
    Source = SharePoint.Tables(SPListURL, [ApiVersion = 15]),
    /*    ListID is the ID of Sharepoint list       */
    SPList = Source{[Id=ListID]}[Items],
    #"Removed Other Columns" = Table.SelectColumns(SPList,{"ContentType"}),
    ContentType1 = #"Removed Other Columns"{0}[ContentType],
    Fields = ContentType1[Fields],
    #"Removed Other Columns1" = Table.SelectColumns(Fields,{"EntityPropertyName", "InternalName", "StaticName", "Title"})
in
    #"Removed Other Columns1";

Step 5:

I think this step is where the important stuff is.

First, I did a left outer join with ListColumnNames (Step3) and Title (Step 4) on EntityPropertyName and Column1. Then expanded and removed unnecessary columns. I ended up with Origin Name and Title (Display Name name in the SharePoint list). Then I created a custom column with a List of original column names.

In step 6, I want to rename all columns based on the display columns, but if we have duplicate columns or if there is already a column with the same name, Table.RenameColumns function will fail.

So I created a custom column with a list of original column names to see if the column already exists. Then I used List.Contains to check if the column already exists and I filtered out all columns which already exists.

In the end, I left again with Original Name and Title but excluded duplicates and columns already exist in the SharePoint List, then I transformed rows to list using Table.TransformRows function.

RenamedColumns = let
    Source = Table.NestedJoin(ListColumnNames,{"Column1"},Title,{"EntityPropertyName"},"Title",JoinKind.LeftOuter),
    #"Expanded Title" = Table.ExpandTableColumn(Source, "Title", {"EntityPropertyName", "Title"}, {"EntityPropertyName", "Title.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Title",{{"Column1", "Original Name"}, {"Title.1", "Title"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([EntityPropertyName] <> null)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Original Name", "Title"}),
    AddListColumns= Table.AddColumn(#"Removed Other Columns", "Custom", each Table.ColumnNames(SPList)),
    CheckNameAlreadyExists = Table.AddColumn(AddListColumns,"TF",each List.Contains([Custom],[Title])),
    #"Filter already existing column names" = Table.SelectRows(CheckNameAlreadyExists, each [TF] = false),
    #"Removed Duplicates" = Table.Distinct(#"Filter already existing column names", {"Title"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Removed Duplicates",{"Original Name", "Title"}),
    MakeAList= Table.TransformRows(#"Removed Other Columns1", Record.FieldValues)
in 

    MakeAList;

Step 6:

The final step for the template!

Again I referenced my query from Step1, SPList. Then used function Table.RenameColumns to rename columns as display names.  That’s all! Your lovely SharePoint list is ready for the transformations and analysis.

 #"Sharepoint List" = let
    Source = SPList,
    RenameColumns= Table.RenameColumns(Source,RenamedColumns,MissingField.UseNull)
in
    RenameColumns;

Summary :

Lately, one slogan I have heard among people is “We are in digital transformation”. The first place they start or probably the easiest place to start is moving all important Excel workbooks to SharePoint Online then analyse them using Power BI. But it was not that simple to make it work, at least not for me. I could write stories about it. I hope I will find some time to write about my adventures of successfully scheduling a Power BI Model with SharePoint list data source…

Sometimes you face irritating problems, If a user changes a column, it breaks the model, because of renaming columns to friendly names as it looks like in the Sharepoint List. I can’t imagine the frustration of a user when a model brakes with a name change error. Luckily I did not work with really wide lists. But with the very first model I designed with SP list, I realised this issue. My best friend google betrayed me, and I left with no choice other than exploring all the metadata I extracted from Sharepoint List.

This Template helped me to speed up the process. Now I don’t need to create Models for users; if they want they can design their own models, the way they want. It’s a win win 🙂

It’s not only me; there are other are others had similar issues

https://community.powerbi.com/t5/Desktop/Column-Name-Changes-when-Sharepoint-list-is-imported-in-PBI/td-p/183814

https://community.powerbi.com/t5/Issues/Sharepoint-list-connector-pulling-in-incorrect-column-names/idi-p/138924

I hope this helps someone out there. This is just a workaround; it worked great for me, hope it works for you too 😊

Sharepoint Power BI Template  – https://1drv.ms/u/s!Avm7gbgZtlMlqCHE55X7yJaDuBx0

Until next time,

Prathy 🙂

 

6 comments
2 FacebookTwitterPinterestEmail