Forum Discussion
Transforming Null values based on Inserting text based another value of another column
Afternoon All,
I am having a challenge where trying to transform some of my data where I have blanks . Below is an example of some the data working with
| Asset Name | ISIN |
| Product Cash | |
| M&G Property Portfolio Sterling I Acc GBP | GB00B8FYD926 |
| Sherwood Unit Trust | |
| IM Cash | |
| BlackRock Continental European Income Fund D Inc | GB00B3Y7MQ71 |
| DM Property |
and this is how i would like to transform the data by updating the blanks based on the value held under "Asset Name"
| Asset Name | ISIN |
| Product Cash | Cash |
| M&G Property Portfolio Sterling I Acc GBP | GB00B8FYD926 |
| Sherwood Unit Trust | No ISIN1 |
| IM Cash | Cash |
| BlackRock Continental European Income Fund D Inc | GB00B3Y7MQ71 |
| DM Property | No ISIN2 |
I have tried using Text.Insert but can only do this where filtering so can't capture all requirements
Any help would be appreciated
Thanks
Will adding conditional custom column help?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY6xDoIwFEV/5aUzQ8UEdLQghKEJig4EHUip0oB9prQx/r2AkcTx5uaec6uK5AYbJyxE9dASjwC5ehXhF0epH6Qwtk9p7BtyNPaGvUIorDS90nfIYCcEpCwfZymjlG2SMt76wUwoWmleiA2ctbJwMm6wCz3j/zbW16I7ouggQm2VltrWPezd5K41ZFrgQ0LidAPxlH6+dRnyQ7iaGTFfvn651w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Asset Name" = _t, ISIN = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Asset Name", type text}, {"ISIN", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Asset Name], "Cash") then "Cash" else if Text.Contains([Asset Name], "Sherwood") then "No ISIN1" else if Text.Contains([Asset Name], "DM Property") then "No ISIN2" else [ISIN]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ISIN"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "ISIN"}}) in #"Renamed Columns"or transform column version
= Table.ReplaceValue(Source," ", each if Text.Contains([Asset Name],"Cash") then "Cash" else if Text.Contains([Asset Name],"Sherwood") then "No ISIN1" else if Text.Contains([Asset Name],"DM Property") then "No ISIN2" else [ISIN],Replacer.ReplaceText,{"ISIN"})
7 Replies
Will adding conditional custom column help?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY6xDoIwFEV/5aUzQ8UEdLQghKEJig4EHUip0oB9prQx/r2AkcTx5uaec6uK5AYbJyxE9dASjwC5ehXhF0epH6Qwtk9p7BtyNPaGvUIorDS90nfIYCcEpCwfZymjlG2SMt76wUwoWmleiA2ctbJwMm6wCz3j/zbW16I7ouggQm2VltrWPezd5K41ZFrgQ0LidAPxlH6+dRnyQ7iaGTFfvn651w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Asset Name" = _t, ISIN = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Asset Name", type text}, {"ISIN", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Asset Name], "Cash") then "Cash" else if Text.Contains([Asset Name], "Sherwood") then "No ISIN1" else if Text.Contains([Asset Name], "DM Property") then "No ISIN2" else [ISIN]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ISIN"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "ISIN"}}) in #"Renamed Columns"or transform column version
= Table.ReplaceValue(Source," ", each if Text.Contains([Asset Name],"Cash") then "Cash" else if Text.Contains([Asset Name],"Sherwood") then "No ISIN1" else if Text.Contains([Asset Name],"DM Property") then "No ISIN2" else [ISIN],Replacer.ReplaceText,{"ISIN"})Ideally would like to head down the transform route however when using your code , it does not seem to make any changes - Have tried using both "" and "null" withour any impact . Looks like the code runs but physically does not make any changes in the table
That is because you are trying to replace "null". There are no "null"s as texts, there are no nulls as objects in your column. I have used " ", since there is a blank space in your column to be replaced. At least in table you have posted initially.
So try with " " or null, since I have no insight in your previous step.