Power Query

[GPnew]

I am newish to power query and have been struggling with the following when I think it should be quite simple

I have loaded data from a pdf file. On one of the 30 or so lines of imported data that should be in the [Name} column , appears incorrectly in the [Employee_No] column. Therefore a need to move it the [Name] column. The text that needs to be moved contains the string "Tal"
This is the code I tried followed by the error message I got

TidyUpData = Table.ReplaceValue( #"Renamed Columns", each if Text.Contains([Employee_No], "Tal")=true then Text.Replace([Name],[Employee_No] ,"") else null),


Expression.Error: 2 arguments were passed to a function which expects 5.
Details:
Pattern=
Arguments=

 

[combo]

The syntax of Table.ReplaceValue:

Table.ReplaceValue(table as table, oldValue as any, newValue as any, replacer as function, columnsToSearch as list) as table

So:
TidyUpData = Table.ReplaceValue( #"Renamed Columns", each [Name], each if Text.Contains([Employee_No], "Tal")=true then [Employee_No] else [Name], Replacer.ReplaceText, {"Name"}),

You with stay with unchanged [Employee_No] column, that may need either new pass or helper column before to change.

combo

 

[GPnew]

Hi it is still not working.

I tried this to test the else...ie to see if the "TAL" was recognised. It is
TidyUpData = Table.ReplaceValue( #"Renamed Columns", each [Name], each if Text.Contains([Employee_No], "Tal")=true then [Employee_No] else "Bla", Replacer.ReplaceText, {"Name"}),

and "Bla" appeared in the Name column in all the lines when there wasn't a "Tal" in the [Employee_No] column ....which means the test works However on the lines where there was a "TAL" in the [Employee_No] there was no change in the [Name] column....
So it has something to do passing the data from the [Employee_No] to the [Name] column once the text is identified. I am not sure if it make a difference but there was a Null in the [Name} column before and there is a third column of data "Total" if that makes a difference ?

 

[combo]

Text.Contains(text as nullable text, substring as text, optional comparer as nullable function) as nullable logical

You need the optional third argument:
Text.Contains([Employee_No], "Tal", Comparer.OrdinalIgnoreCase)



combo

 

[GPnew]

Still no Joy ? Frustrating

This is whst I have
TidyUpData = Table.ReplaceValue( #"Renamed Columns", each [Name], each if Text.Contains([Employee_No], "TAL",Comparer.OrdinalIgnoreCase)=true then [Employee_No] else [Name], Replacer.ReplaceText, {"Name"}),

FYI I tried adding a new column "Dept2" to see if I could move the data there ..and it worked eg
ExtractDeptNotTotal = Table.AddColumn( TidyUpData, "Dept2", each if Text.Contains([Employee_No], "TAL")=true then Text.Replace([Employee_No],"Dept2","") else null),

Any more suggestions

 

[GPnew]

Worked it out
Apparently you cant save into a column/cell row type null. Therefore inserted a row to convert to unspecified..

RemoveNulls = Table.TransformColumns(#"Renamed Columns", {"Name", each if _ is null then "unspecified" else _}),
TidyUpData = Table.ReplaceValue( RemoveNulls, each [Name], each if Text.Contains([Employee_No], "TAL",Comparer.OrdinalIgnoreCase)=true then [Employee_No] else [Name], Replacer.ReplaceText,{"Name"}),

Thanks for your help