topic M language issue in Developer

M language issue

dslForPBI — Tue, 13 Mar 2018 16:14:29 GMT

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?

Re: M language issue

Greg_Deckler — Tue, 13 Mar 2018 16:23:00 GMT

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

Re: M language issue

dslForPBI — Tue, 13 Mar 2018 17:10:30 GMT

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])),

Re: M language issue

Greg_Deckler — Tue, 13 Mar 2018 17:26:38 GMT

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])),

Re: M language issue

dslForPBI — Tue, 13 Mar 2018 17:39:40 GMT

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)..

Re: M language issue

Greg_Deckler — Tue, 13 Mar 2018 18:16:44 GMT

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?

Re: M language issue

dslForPBI — Tue, 13 Mar 2018 18:32:39 GMT

..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:

...and so on.

Re: M language issue

Greg_Deckler — Tue, 13 Mar 2018 18:46:14 GMT

OK, invoking @ImkeF.

Re: M language issue

Greg_Deckler — Tue, 13 Mar 2018 18:50:48 GMT

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

Re: M language issue

dslForPBI — Tue, 13 Mar 2018 19:25:51 GMT

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!!

Re: M language issue

ImkeF — Tue, 13 Mar 2018 19:49:29 GMT

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}