passing a list to conditional statement

[Anonymous]

hi all,

i have two projects that need similar concept. 

One is where i need to add missing client codes. The source data has had fields that have been repurposed over time.  At least 90% of the clients have codes in one of the 5 columns,   i have a market list where I would normally merge to table.  Instead I want to evaluate the market list against these columns and if value exists return it to column otherwise leave blank.   Was going to merge columns for conditional statement and then expand.  Found Imke idea to unpivot the columns and re-pivot after performing conditional  statement.    I have found several options to pass lists as filter arguments.  I need all records to return.  My attempt to parameterize conditional statement returned all nulls. 

The second project is similar in that i need to identify ads placed in sports games.  We have a daypart field for sports but is optional so have a lot of data that fell into prime...every program during prime time tv.  No uniformity in progam naming and abbreviations.  huge file.  anyway i have compiled lists of games, leagues, teams, etc...  i need to categorize the records for easy filtering.  i need to identify sports, basically that would be any record where any item in the list shows up.  from there i need to specify the game in one filter, the teams in next filter, leagues, etc.... 

if value = {list of criteria} then {list of criteria value} else null

just not sure how to pass the value to the conditional statement. 

thanks

Theresa

[v-frfei-msft]

Hi @Anonymous ,

Please refer to this case and hope it can help you.

https://community.powerbi.com/t5/Desktop/Pass-a-list-as-filter-argument-in-Power-Query/td-p/149101 

If it doesn't meet your requirement,  Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[Anonymous]

bumping thread.  think sending before holiday wasn't best idea.  any suggestions? 

[Anonymous]

Sample File link    ok  here's the link to a very truncated/altered file.  i had to recreate the function where i was passing a parameter to the conditional statement.   all the result values returned as null..   i added a client code of  XXXX that should return a value on the altered data. 

the goal is to dynamically assign the client code if it is found in the values list.  on the other process, I want to dynamically categorize the data, with each category becoming more specific.  in VBA I would just create a variable and loop.  I am still getting tripped up on the way variables work in other languages versus PQ.    Thx T

[Anonymous]

this is my first attempt at sharing a link, so we will see how it goes.

i keep pasting but can't see anything.  The message shows some placeholders but that is it.

Please let me know if this come thru on that end.  Thx T

[Anonymous]

ok.  I had seen that topic before i posted.  they are filtering data.  I am wanting to categorize the data for future filtering.   tried playing around with the parameters and just confused myself.   let me see if i can figure out how to load the sample file for you to see.

[Anonymous]

Hi,  I apologize.. just saw message this am. Kinda sorta on vacation, so probably not the best time to submit a request.

 I have a file ready to load but let me test this potential solution first. 

Thx

T