cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kev_bro
Frequent Visitor

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 NameISIN
Product Cash 
M&G Property Portfolio Sterling I Acc GBPGB00B8FYD926
Sherwood Unit Trust 
IM Cash 
BlackRock Continental European Income Fund D IncGB00B3Y7MQ71
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 NameISIN
Product CashCash
M&G Property Portfolio Sterling I Acc GBPGB00B8FYD926
Sherwood Unit Trust

No ISIN1

IM CashCash
BlackRock Continental European Income Fund D IncGB00B3Y7MQ71
DM PropertyNo 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 

 

1 ACCEPTED SOLUTION
Jakinta
Solution Sage
Solution 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"})

 

View solution in original post

7 REPLIES 7
kev_bro
Frequent Visitor

@Jakinta 

 

adding conditional custom column worked 

 

Thanks 

kev_bro
Frequent Visitor

@Jakinta 

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 

kev_bro_1-1620832247952.png

 

 

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.

kev_bro
Frequent Visitor

as mention previous reply I have  tried using both "" and "null" withour any impact

As i mentioned in previous reply  you did not try " "(not equal to "") or null ( not equal to "null"). 

So we are dealing here with 4 different cases: 3 of them of type text, 1 of null type (green).

Jakinta_0-1620846006133.png

 

 

kev_bro
Frequent Visitor

Hi Jakinta , I am still not having any luck ....so not sure what I am doing wrong 

I have tried all 4 variations , and whilst no error is shown in the syntax there are no changes in the column as expected 

 

1) " "

kev_bro_0-1620910665147.png

2) ""

kev_bro_1-1620910743747.png

 

3) "null"

kev_bro_2-1620910811661.png

 

4) null

kev_bro_3-1620910886770.png

in resepct to changing the cunditional custome column where / how do I execute your below statement to try and see if that will work ( sorry still new to the Power BI so when I see the below " Let " statement I am not aware on the process to execute in Power BI 

 

Thanks for the help, most appreciated 

 

Jakinta
Solution Sage
Solution 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"})

 

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Kudoed Authors