Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rpiboy_1
Resolver I
Resolver I

Column Reference with text string

I've written a function to peform condtional text replacement over multiple columns. However, when I try to write the code as a tru function I'm running into issues with a Column Reference, where my code requires a [ColumnName], but in functions you can't pass a Column, you can only pass a text string as the ColumnName. How do I convert the text string to the proper format to call the Column? I thought I had come up with a solution, but it does not work.

 

 

 

(tbl as table, ReplaceColumn as text, ConditionalColumn as text)=>
let
    #"ColumnAsList" = Table.FromList( Table.Column(tbl, ConditionalColumn) ),
    #"Replaced Value" = Table.ReplaceValue(
        tbl,
        each ReplaceColumn,
        each if #"ColumnAsList"[Column1] = 2 //[Room Data status code] = 2
            then null
            else if #"ColumnAsList"[Column1] = 0//[Room Data status code] = 0
            then ""
            else Record.Field(_, ReplaceColumn),//[Room Data Status],
        Replacer.ReplaceValue,
        {ReplaceColumn})
in
    #"Replaced Value"

 

 

 

The arguement 'ConditoinalColumn' is the string in question.  You can see where in #"ColumnAsList" I thought I could get the column from the table as a list, then convert it to a single column table. Then I assumed I'd be able to reference the default 'Column1' from this 'temporary' table in the #"Replaced Value" step. However, that does not seem to work.

 

Not that this query works fine, when it is in context and targeting a specific column, in which case I can hard code the column reference as appropriate with [ColumnName].

 

Appreciate any suggestions!

1 ACCEPTED SOLUTION

The final solution ended up being much, much simpler. I realized it was way easier to insert a Table.RenameColumns function at the start, and rename the two incoming columns to values that I control, go through my Find/Replace function. Then rename the columns back to what they were at the start. I also had to clean-up my data type coming out of the Find/Replace function. Is there a method to direclty reference the Record, maybe, but this is pretty elegant and straight-forward.

(tbl as table, ReplaceColumn as text, ConditionalColumn as text)=>
let
    #"RenameReplaceColumn" = Table.RenameColumns(tbl,{{ReplaceColumn, "ReplaceColumnName"}, {ConditionalColumn, "CondColumnName"}}),
    #"Replaced Value" =
        Table.ReplaceValue(
            #"RenameReplaceColumn",
            each [ReplaceColumnName],
            each if [CondColumnName] = 2 
                then null
                else if [CondColumnName] = 0
                then ""
                else Record.Field(_, "ReplaceColumnName"),
            Replacer.ReplaceValue,
            {"ReplaceColumnName"}
        ),
    #"ChangeTypeText" = Table.TransformColumnTypes(#"Replaced Value", {{"ReplaceColumnName", type text}}),
    #"RenameReplaceColumn1" = Table.RenameColumns(#"ChangeTypeText",{{"ReplaceColumnName", ReplaceColumn}, {"CondColumnName", ConditionalColumn}})
    
in
    #"RenameReplaceColumn1"



View solution in original post

2 REPLIES 2
watkinnc
Super User
Super User

In your function parameters, try changing to  (til as table, valueToSearch as record, ReplaceColumn as text)

Then change "each ReplaceColumn" with "each ColumnToSearch". Leave your "Replacer.ReplaceValue({ReplaceColumn})" as is.

 

Seems that your issue is that you are using each  with a function that returns "some text value", but each has to be used with a record, if I know my M correctly, so keep that in mind if my solution gives you any trouble.

 

--Nate

 

 


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

The final solution ended up being much, much simpler. I realized it was way easier to insert a Table.RenameColumns function at the start, and rename the two incoming columns to values that I control, go through my Find/Replace function. Then rename the columns back to what they were at the start. I also had to clean-up my data type coming out of the Find/Replace function. Is there a method to direclty reference the Record, maybe, but this is pretty elegant and straight-forward.

(tbl as table, ReplaceColumn as text, ConditionalColumn as text)=>
let
    #"RenameReplaceColumn" = Table.RenameColumns(tbl,{{ReplaceColumn, "ReplaceColumnName"}, {ConditionalColumn, "CondColumnName"}}),
    #"Replaced Value" =
        Table.ReplaceValue(
            #"RenameReplaceColumn",
            each [ReplaceColumnName],
            each if [CondColumnName] = 2 
                then null
                else if [CondColumnName] = 0
                then ""
                else Record.Field(_, "ReplaceColumnName"),
            Replacer.ReplaceValue,
            {"ReplaceColumnName"}
        ),
    #"ChangeTypeText" = Table.TransformColumnTypes(#"Replaced Value", {{"ReplaceColumnName", type text}}),
    #"RenameReplaceColumn1" = Table.RenameColumns(#"ChangeTypeText",{{"ReplaceColumnName", ReplaceColumn}, {"CondColumnName", ConditionalColumn}})
    
in
    #"RenameReplaceColumn1"



Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors