Query Editor Replace Row Values of Column A, if Column B contains List Values **Difficulties**

[nowaka]

Background

I have a sheet with 10.000+ rows of variables with explanations that others should be able to select easily. In the process of simplifying the selection process, I end up with a list of selected variables. This list should be utilized to put a "x" in the [select] column of the original sheet, for rows that contain values present in the "selected variables" list in the [varname] column.

My attempt

The original sheet was turned into a table called result_table. The selected list of variables was turned into a list called selected_vars. A select column is present in result_table, which was attempted to populate with "x" where the varname column contained the values from selected_vars by using Table.ReplaceValue. 

The Query M code of my attempt looks like this:

let
    Source = Excel.CurrentWorkbook(){[Name="result_table"]}[Content],
    Variables = selected_vars,
    #"Replaced Value" = Table.ReplaceValue(Source,null,each if List.Contains(Variables, [varname]) then "x" else null,Replacer.ReplaceValue,{"select"})
in
    #"Replaced Value"

I kept the selected_vars list simple for the purpose of testing the Query:

A preview of the Source from result_table:

When I run the M code for all steps, the Query Editor attempts to refresh, but it takes ages so I have never seen it complete the task. 
When I try to filter the query manually, by selecting the "subject_id" and "site" variable, the query refreshes within seconds:

What am I doing wrong? Is there another, more effective way of reaching my goal?

[Anonymous]

@nowaka,

Add  two blank queries in Power BI Desktop, paste the following code to Advanced Editor of the blank queries. Then check if you get issues when refreshing.

let
    Source = Excel.Workbook(File.Contents("yourfolder\TEST Sample.xlsm"), null, true),
    result_table_Table = Source{[Item="result_table",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(result_table_Table,{{"select", type any}, {"data_id", type text}, {"varname", type text}, {"pubready", type any}, {"group", type text}, {"visit", type text}, {"category", type any}, {"varlab", type text}, {"vallab", type text}}),
    Variables = selected_vars,
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type" ,null,each if List.Contains(Variables, [varname]) then "x" else null,Replacer.ReplaceValue,{"select"})
in
    #"Replaced Value"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKi5NykpNLonPTFGK1QFyM0tSlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [List = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"List", type text}}),
    List = #"Changed Type"[List]
in
    List

Regards,
Lydia

[nowaka]

Hello everyone!

Is it possible to use a dynamic string inside a function?

Explanation

I want to replace a value of rows in ColumnA where ColumnB is equal to one of the values in the dynamaic ListX. I have made a topic regarding this problem here. Since simply using List.Contains entails unknown complications, I want to explore what other possibilites i have to solve my problem. When i choose the "values" in ColumnB that need to be equal to the values in ListX manually, I get what I want:

Table.ReplaceValue(Source,null,each if ([varname] = "subject_id" or [varname] = "ocl_data_version" or [varname] = "site" or [varname] = "latest_date" or [varname] = "latest_visit" or [varname] = "latest_visit_num") then "x" else "testtest",Replacer.ReplaceValue,{"select"})

 But this function is not created automatically when the list changes. To solve this, I combined the values of ListX:

With "text" & Text.Combine() & "text" to create the text needed in the if condition:

I have named the combined values "String". When I add place String as the if condition, the query loads without any effect of the "ReplaceValue" step. 

How would I go about using String as the if condition successfully? If possible, that is.

/nowaka

[MarkS]

Hi @nowaka,

Try chnaging to this

#"Replaced Value" = Table.ReplaceValue(Source,null,each if List.ContainsAny(Variables, [varname]) then "x" else null,Text.Replace,{"select"})

and see if it works any better

[nowaka]

Hi @MarkS

After changing to your suggestion, my query now loads but without having any effect of Table.ReplaceValue():

These are the variables currently selected in selected_vars:

And this is proof of a working example with manually selected values for [varname]: 

[Anonymous]

@nowaka,

Is there any possibilty that you share the Excel file to us? Have you tried to take transformation in Power BI Desktop?

Regards,
Lydia

[nowaka]

Hi Lydia,

I just made a test sample excel file and uploaded it to here:
https://drive.google.com/file/d/15-KREUXApF2xfwbaqUTIsvM5YIcBubkP/view?usp=sharing

Unfortunately, I am not sure how to do this in PowerBI Desktop. I hope this helps.