Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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.
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).
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) " "
2) ""
3) "null"
4) null
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
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"})
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |