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
Responsive Resident
Responsive Resident

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

 

 

Jakinta
Responsive Resident
Responsive Resident

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

Jakinta
Responsive Resident
Responsive Resident

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
Responsive Resident
Responsive Resident

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 Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

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

Top Solution Authors
Top Kudoed Authors