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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dslForPBI
Helper I
Helper I

M language issue

OK..this makes no sense to me..I am trying to take a list of numbers and use them as part of a "List.Contains" for a filter...if I hardcode the number list as in:

 

each List.Contains({1,2,3...

 

the filter works perfectly but if I make the list a parameter, then source that it doesn't, even if I use other functions like "Value.From" or convert it using Binary.From or some other function..it doesn't error out, just doesn't work..what gives? 

10 REPLIES 10
Greg_Deckler
Super User
Super User

When you change it to a parameter, are you sure you are feeding it a list and not a table?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks for your response Greg_Deckler!  I am sure it is not a table--which is why it seems like either an odd bug or something--here is a snippet of code with comments:

//Prep for filter using a parameter (outboundcodes) which is just a comma-delimited list of values
//

//
valForCodes = Value.FromText(outboundcodes),


//This one works (hardcoded)
tblIncludeStates = Table.SelectRows(tblFromDownload, each List.Contains({543,95,96,1359,1360,1104,1170,1172,93,1248,454,1361,92,97,-4,-3,-5,-7,-8,-2}, [Outstate_Code])),

//This one doesn't (just results in an empty table

//
tblIncludeStates = Table.SelectRows(tblFromDownload, each List.Contains(valForCodes,[Outstate_Code])),

Try this:

 

//Prep for filter using a parameter (outboundcodes) which is just a comma-delimited list of values
//
//
valForCodes = Text.ToList(Value.FromText(outboundcodes)),

//This one works (hardcoded)
tblIncludeStates = Table.SelectRows(tblFromDownload, each List.Contains({543,95,96,1359,1360,1104,1170,1172,93,1248,454,1361,92,97,-4,-3,-5,-7,-8,-2}, [Outstate_Code])),
//This one doesn't (just results in an empty table
//
tblIncludeStates = Table.SelectRows(tblFromDownload, each List.Contains(valForCodes,[Outstate_Code])),

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Right..tried that..as well as trying to convert it to binary first..you just get an empty table as a result (no errors, data is fine when viewed in the applied steps)..like the filtering suddenly doesn't work..only other thing I haven't tried is changing the encoding value when converting to binary first, like perhaps the text encoding is different with hand-input values within the advanced editor (is all I can think of)..

What does your raw data look like? Is it just that comma-delimited text or ? Can you post what your raw data looks like or an example/sample of what it looks like?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

..sure..so what's happening is as I showed in the previous snippet:

 

tblIncludeStates = Table.SelectRows(tblFromDownload, each List.Contains({543,95,96,1359,1360,1104,1170,1172,93,1248,454,1361,92,97,-4,-3,-5,-7,-8,-2}, [Outstate_Code])),

 

..this works fine when you have it like this on raw data like Outstate_Code = 543, Outstate_Ode = 95, etc (it includes the rows in output)..but if I try and use parameter "P1" where P1 = 543,95,96..it fails..I also figured out why Text.ToList fails because the list becomes:

5

4

3

,

9

5

...and so on.

I did come up with this, not sure if it helps you or not though. But, I end up with a List of values:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("FYmxDQAgCMB+cS6JCKjcYvz/DXFpmvacFm5kkBO1yMLsqHYvrG9rkIYO33j4/0pWW4gjhgRSvpHR7n0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [MyList = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MyList", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "CSV", each Csv.Document([MyList],null,",")),
    #"Expanded CSV" = Table.ExpandTableColumn(#"Added Custom", "CSV", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20"}, {"CSV.Column1", "CSV.Column2", "CSV.Column3", "CSV.Column4", "CSV.Column5", "CSV.Column6", "CSV.Column7", "CSV.Column8", "CSV.Column9", "CSV.Column10", "CSV.Column11", "CSV.Column12", "CSV.Column13", "CSV.Column14", "CSV.Column15", "CSV.Column16", "CSV.Column17", "CSV.Column18", "CSV.Column19", "CSV.Column20"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded CSV", {"MyList"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"MyList", "Attribute"}),
    Value = #"Removed Columns"[Value]
in
    Value

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Nice..unfortunately no, it doesn't work..that List.Contains is doing something really weird which I suspect has something to do with encoding.  You end up with an empty list as a result..thanks though for all your efforts!!

This sounds as if your parameter isn't actually a list, but a text-string (otherwise the function Text.ToList wouldn't work)

You said your parameter is: P1 = 543,95,96

To make this a list, you would have to wrap it into curly brackets like this: P1 = {543, 95, 96}

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

OK, invoking @ImkeF.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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