Errors when grouping by list in Power Query

1
votes

I have a set of unique items (Index) to each of which are associated various elements of another set of items (in this case, dates). In real life, if a date is associated with an index, an item associated with that index appeared in a file generated on that date. For combination of dates that actually occurs, I want to know which accounts were present.

let
Source = Table.FromRecords({
    [Idx = 0, Dates = {#date(2016,1,1), #date(2016,1,2), #date(2016,1,3)}],
    [Idx = 1, Dates = {#date(2016,2,1), #date(2016,2,2), #date(2016,2,3)}],
    [Idx = 2, Dates = {#date(2016,1,1), #date(2016,1,2), #date(2016,1,3)}]}, 
    type table [Idx = number, Dates = {date}]),

// Group by 
Grouped = Table.Group(Source, {"Dates"}, {{"Idx", each List.Combine({[Idx]}), type {number}}}),

// Clicking on the item in the top left corner generates this code:
Navigation = Grouped{[Dates={...}]}[Dates],
// Which returns this error: "Expression.Error: Value was not specified"

// My own code to reference the same value returns {0,2} as expected.
CorrectValue = Grouped{0}[Idx],

// If I re-make the table as below the above error does not occur.
ReMakeTable = Table.FromColumns(Table.ToColumns(Grouped), Table.ColumnNames(Grouped))

in ReMakeTable

It seems that I can use the results of this in my later work even without the Re-make (I just can't preview cells correctly), but I'd like to know if what's going on that causes the error and the odd code at the Navigation step, and why it disappears after the ReMakeTable step.

2
powerquerym
It seem to me that you group table by list of dates, and get list of items corresponding to each list of dates, do you? If so, I hardly understand how you are going to use it further, since it probably would generate a lot of unique date lists with only one (maybe two) corresponding indexes. – Eugene

2 Answers

1
votes

This is a bug in the UI. The index the UI calculates is incorrect: it should be 0 instead of [Dates={...}]. ... is a placeholder value, and it generates the "Value was not specified" exception if it is not replaced.

1
votes

This happens because when you double click an item, the auto-generated code uses value filter instead of row index that you are using to get the single row from the table. And since you have a list as a value, it should be used instead of {...}. Probably UI isn't capable to work with lists in such a situation, and it inserts {...}, and this is indeed an incorrect value.

Thus, this line of code should look like:

    Navigate = Grouped{[Dates = {#date(2016,1,1), #date(2016,1,2), #date(2016,1,3)}]}[Idx],

Then it will use value filter.