×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Power Query

Power Query

Power Query

(OP)

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=[List]

RE: Power Query

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

RE: Power Query

(OP)
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 ?

RE: Power Query

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

RE: Power Query

(OP)
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

RE: Power Query

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close