Forum Discussion
Remove Null Values by Moving Values
Hi swwong1 ，
The solution in above thread can't get your desired result base on my testing. Please copy and paste the below applied codes in your Advanced Editor and check whether it can be used as a workaround... You can find the details in the attachment.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg12VNJRckoszk4tSUrMyQFyQChWByYVXJ6Zm5uZl44hYWhgkIshCESORckZqUWVMHHH0uKSosSczETsZiFLQ5BTYgpQTUl+HljeOSMzDyQXlJmWk4qkDyYekpiUk6oQkpqXl1mMRRqLCjRpNOciySA7ODYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, Gold = _t, Silver = _t, Bronze = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Gold", type text}, {"Silver", type text}, {"Bronze", type text}}), #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Gold", "Silver", "Bronze"}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Only Selected Columns", each ([Value] <> "")), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Country","Attribute"}, {{"Value", each Text.Combine([Value],"; "), type text}}), #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Attribute]), "Attribute", "Value") in #"Pivoted Column"Best Regards
amitchandak, many thanks for looking into it.
The below is the StackOverflow link which provides the solution I require but I am not sure where to put this code in PQ/PBI.
https://stackoverflow.com/questions/64802547/move-up-values-when-null-power-query
Hi swwong1 ，
The solution in above thread can't get your desired result base on my testing. Please copy and paste the below applied codes in your Advanced Editor and check whether it can be used as a workaround... You can find the details in the attachment.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg12VNJRckoszk4tSUrMyQFyQChWByYVXJ6Zm5uZl44hYWhgkIshCESORckZqUWVMHHH0uKSosSczETsZiFLQ5BTYgpQTUl+HljeOSMzDyQXlJmWk4qkDyYekpiUk6oQkpqXl1mMRRqLCjRpNOciySA7ODYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, Gold = _t, Silver = _t, Bronze = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Gold", type text}, {"Silver", type text}, {"Bronze", type text}}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Gold", "Silver", "Bronze"}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Only Selected Columns", each ([Value] <> "")),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Country","Attribute"}, {{"Value", each Text.Combine([Value],"; "), type text}}),
#"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Attribute]), "Attribute", "Value")
in
#"Pivoted Column"
Best Regards