Power Query to conditionally combine columns and sort

Question

I am learning to use power query but am struggling with this simple task.

I worked out that the addcolumn was misbehaving because of the null values in the PreferredName column hence the #"Replaced Value" since the
"if Text.From([PreferredName]) <> "" then [PreferredName] else [FormalName]"
on its own didn't work.

I want to display the result as FormalName followed by an added column called Name then Role.
The added column should contain PreferredName if it is not empty otherwise the FormalName.

The output should extract all entries with a FormalName of "Vacant" and list these individually at the end (ideally sorted by Role though that is a refinement)

Other rows should be consolidated other to show distinct Name with all values of Role.

The output below is the result of the query below. What I don't understand is how to keep the original values for FormalName and PreferredName from the non-vacant entries

Any help will be appreciated.

Regards
TonyS

Source table is as follows

FormalName	PreferredName	Role
Annie Jayman	Annie (Anjay) Jayman	Role 1
Jamie T		Jr1
Vacant		R6
Jamie T		Jr2
Martin Jackson	Martin (Mace) Jackson	R3
Martin Jackson	Martin (Mace) Jackson	R4
Vacant		R5

Result of query table

Name	Role	FormalName	PreferredName
Annie (Anjay) Jayman	Role 1
Jamie T	Jr1, Jr2
Martin (Mace) Jackson	R3, R4
Vacant	R6	Vacant
Vacant	R5	Vacant

let <br> // Load your table (replace "YourTableName" with the actual table name) <br> Source = Excel.CurrentWorkbook(){[Name="YourTableName"]}[Content], <br> <br> //Convert null to empty string to avoid errors in the following test <br> #"Replaced Value" = Table.ReplaceValue(Source,null,"",Replacer.ReplaceValue,{"FormalName","PreferredName"}), <br> <br> // Add a conditional column for "Name" <br> AddNameColumn = Table.AddColumn(#"Replaced Value", "Name", each if Text.From([PreferredName]) <> "" then [PreferredName] else [FormalName]), <br> <br> // Filter rows where "FormalName" is not "Vacant" <br> NonVacantRows = Table.SelectRows(AddNameColumn, each [FormalName] <> "Vacant"), <br> <br> // Group rows by "Name" and aggregate roles <br> GroupedRows = Table.Group(NonVacantRows, "Name", {{"Role", each Text.Combine([Role], ", "), type text}}), <br> <br> // Filter rows where "Name" is not "Vacant" <br> NonVacantDistinctRows = Table.Distinct(Table.SelectRows(GroupedRows, each [Name] <> "Vacant")), <br> <br> // Append the "Vacant" rows to the end of the list <br> CombinedRows = NonVacantDistinctRows & Table.SelectRows(AddNameColumn, each [FormalName] = "Vacant") <br> <br>in <br> CombinedRows

Answer 1

Hi,

I cannot understand why you need the last 2 columns as seen in your result table. Anyways, try this M code

let

Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], 

#"Changed Type" = Table.TransformColumnTypes(Source,{{"FormalName", type text}, {"PreferredName", type text}, {"Role", type text}}), 

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [PreferredName]=null then [FormalName] else [PreferredName]), 

#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"FormalName", "PreferredName"}), 

#"Grouped Rows" = Table.Group(#"Removed Columns", {"Custom"}, {{"Roles", each Text.Combine([Role], ", "), type nullable text}}), 

#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Custom", Order.Ascending}}) 

in

#"Sorted Rows"

Hope this helps.

Answer 2

Hi Ashish, Thanks for your reply. It helped me resolve the issue.

You are correct that I don't need the last two columns, they were there for visual comparison. I have left only the "Formal Name"
column for the same reason. I don't understand though, why it doesn't show the original values.

Below is the query I ended up with. I hacked it to get it to do what I want.

I still had problems with null values so I replaced them at the start.

I ended up refining the Role descriptions by also adding the value from Program\Department

The code after the marked place I have marked with //******// seems a bit clunky to my but it works. I am still puzzled as to why the FormalName column doesn't show the original values for the rows with FormalName value other then vacant

	let <br> <br> Source = Excel.CurrentWorkbook(){[Name= "YourTableName"]}[Content],<br><br> //Replace nulll with text equivalent so that null doesn't screw with the comparisons later<br><br> ChangedType = Table.ReplaceValue(Source,null,"",Replacer.ReplaceValue,{"FormalName", "PreferredName", "Program\Department" , "Role"}), <br> <br> //Create a Name column and populate it based on whether there is a PreferredName <br> AddedNameColumn = Table.AddColumn(ChangedType, "Name", each if [PreferredName]="" then [FormalName] else [PreferredName]), <br> <br> // Create a CustomRoleDept column by combining the Role and Program\Department <br> AddedCustom = Table.AddColumn(AddedNameColumn, "CustomRoleDept", each if [#"Program\Department"]<>"" then [Role] & " - " & [#"Program\Department"] else [Role]), <br> <br> // Get all rows "Name" is not equal to "Vacant" <br> NonVacantRows = Table.SelectRows(AddedCustom, each [Name] <> "Vacant"), <br> <br> //Get just the rows where the Name is equal to "vacant" so we can consolidate any that have multiple roles <br> VacantRows = Table.SelectRows(AddedCustom, each [Name] = "Vacant"), <br> <br> // SHow only the columns we are working with <br> #"Removed Other Columns" = Table.SelectColumns(VacantRows,{"CustomRoleDept", "FormalName", "PreferredName","Name", "Role"}), <br> <br><br> //******// <br> //Process the VacantRows and move the generated CustomRoleDept value to column Role so we can show it later <br> //This should do the move of the CustomRoleDept value to the Role Column <br> #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Column2", each [CustomRoleDept]), <br> <br> //Remove the old Role column so we can move the contents of the generated CustomRoleDept column <br> #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Role"}), <br> <br> //Rename this back to Role <br> #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column2", "Role"}}), <br> GroupedRows = Table.Group(NonVacantRows, {"Name"}, {{"Role", each if [#"Program\Department"]<>"" then Text.Combine([CustomRoleDept], " / " ) else [CustomRoleDept]}}), <br> <br> //Sort the rows for non vacant positions <br> SortedRows = Table.Sort(GroupedRows,{{"Name", Order.Ascending}}), <br> <br> //Display the combined rows <br> AllRows = SortedRows & #"Renamed Columns", <br> <br> #"Removed Other Columns1" = Table.SelectColumns(AllRows,{"FormalName", "Name", "Role"}) <br> <br>in <br> #"Removed Other Columns1"	<br>	---

Source

FormalName	PreferredName	Role	Program\Department
Annie Jayman	Annie (Anjay) Jayman	Role 1	Dept A
Jamie T		Jr1	Dept B
Vacant		R6	Dept C
Jamie T		Jr2	Dept D
Martin Jackson	Martin (Mace) Jackson	R3	Dept E
Martin Jackson	Martin (Mace) Jackson	R4	Dept F
Vacant		R5	Dept G

Result

FormalName	Name	Role
	Annie (Anjay) Jayman	Role 1 - Dept A
	Jamie T	Jr1 - Dept B / Jr2 - Dept D
	Martin (Mace) Jackson	R3 - Dept E / R4 - Dept F
Vacant	Vacant	R6 - Dept C
Vacant	Vacant	R5 - Dept G

Answer 3

This is a way to simulate table having your data in Power BI (This is generated through Enter Data button in PQ interface).

You will need to replace that Source line with your line

Source = Excel.CurrentWorkbook(){[Name="YourTableName"]}[Content]

So, code will become

let

Source = Excel.CurrentWorkbook(){[Name="YourTableName"]}[Content],

Tbl1 = Table.SelectRows(Source, each ([FormalName] = "Vacant")),

#"Filtered Rows" = Table.SelectRows(Source, each ([FormalName] <> "Vacant")),

Custom1 = Table.ReplaceValue(#"Filtered Rows",each [PreferredName],each if [PreferredName]="" or [PreferredName]=null then [FormalName] else [PreferredName],Replacer.ReplaceValue,{"PreferredName"}),

#"Grouped Rows" = Table.Group(Custom1, {"FormalName", "PreferredName"}, {{"Role", each Text.Combine([Role], ", ")}}),

#"Appended Query" = Table.Combine({#"Grouped Rows", Tbl1}),

#"Renamed Columns" = Table.RenameColumns(#"Appended Query",{{"PreferredName", "Name"}})

in

#"Renamed Columns"

Answer 4

Hi, thanks for the response, but I'm not sure why I should use Json instead of the native Excel Power Query.

Also not sure I should use anything I don't understand such as "(Binary.FromText("i45WcszLy0xV8EqszE3MU9KBcjUc87ISKzURwkH5OakKhkqxOtFKXom5QBUhQEEg8iqCCIYlJifmlUDEgsywqTMCC/omFpVk5gHNTc4uzgcZDBXQ8E1MTtVEEg8yJlG9CaY7TJViYwE=", BinaryEncoding.Base64)"

I will try the other responses first.

Answer 5

A simple transformation steps would like this (replace Source)

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcszLy0xV8EqszE3MU9KBcjUc87ISKzURwkH5OakKhkqxOtFKXom5QBUhQEEg8iqCCIYlJifmlUDEgsywqTMCC/omFpVk5gHNTc4uzgcZDBXQ8E1MTtVEEg8yJlG9CaY7TJViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let \_t = ((type nullable text) meta [Serialized.Text = true]) in type table [FormalName = \_t, PreferredName = \_t, Role = \_t]), 

Tbl1 = Table.SelectRows(Source, each ([FormalName] = "Vacant")), 

#"Filtered Rows" = Table.SelectRows(Source, each ([FormalName] <> "Vacant")), 

Custom1 = Table.ReplaceValue(#"Filtered Rows",each [PreferredName],each if [PreferredName]="" or [PreferredName]=null then [FormalName] else [PreferredName],Replacer.ReplaceValue,{"PreferredName"}), 

#"Grouped Rows" = Table.Group(Custom1, {"FormalName", "PreferredName"}, {{"Role", each Text.Combine([Role], ", ")}}), 

#"Appended Query" = Table.Combine({#"Grouped Rows", Tbl1}), 

#"Renamed Columns" = Table.RenameColumns(#"Appended Query",{{"PreferredName", "Name"}}) 

in

#"Renamed Columns"