Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
When you change it to a parameter, are you sure you are feeding it a list and not a table?
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])),
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?
..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
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.