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
JakintaSolution Sage
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
- Jakinta
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.