Stack overflow after expanding

[aarvidsson]

Hi guys,

I'm running into a stack overflow error after expanding a #shared table. I've done this:

1. I created three excel files with two columns each: name and value. These excel files contain the same names but have different values (imagine these files to be sales data for specific products). They each hold three rows.

2. I've loaded each of them into Power BI Desktop. This is the M code for them (only the first one shown):

let
    Source = Excel.Workbook(File.Contents("C:\Temp\Book1.xlsx"), null, true),
    ProductA_Sheet = Source{[Item="ProductA",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(ProductA_Sheet),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"Value", Int64.Type}})
in
    #"Changed Type"

3. I created a query using #shared like this:

let
    Query1 = #shared,
    #"Converted to Table" = Record.ToTable(Query1),
    #"Kept First Rows" = Table.FirstN(#"Converted to Table",3),
    #"Expanded Value" = Table.ExpandTableColumn(#"Kept First Rows", "Value", {"Name", "Value"}, {"Name.1", "Value.1"})
in
    #"Expanded Value"

As soon as I try to close and save the query editor I get a stack overflow error. I've tried this on both 32 and 64 bit versions of PBI Desktop as well as the January and December versions as well. Same error. Everything works fine until I do the expand operation and try to save. I found a weird behaviour that might or might not be related; this is what I see in the "edit query" before keeping the first three rows:

And after keeping the three rows:

But this is what I see out in the PBI Desktop:

This may or may not be just a bug in the display, or it might be the actual culprit. Can anyone lend me a hand?

[v-haibl-msft]

@aarvidsson

Below is the response from product team:

This is effectively working as designed. Scenarios using #shared are for advanced users who understand its limitations.

Best Regards,

Herbert

[GuyInACube]

How many items do you have in the sheets? I tried to reproduce the issue. I created three product spreadsheets with just name and value columns.  Here was the shared query. (I'm using the January Power BI Desktop.

let
Query = #shared,
Converted = Record.ToTable(Query),
KeptRows = Table.FirstN(Converted ,3),
ExpandedItems = Table.ExpandTableColumn(KeptRows, "Value", {"Name", "Value"}, {"Name.1", "Value.1"})
in
ExpandedItems

[aarvidsson]

Hi Adam,

The problem won't show itself until you close and apply back to PBI desktop. Then the resulting query will be blank. This is probably due to some scoping issue with #shared and #section (and equally probably by design), but I can't get my head around how it's supposed to work. In your case, using FirstN, I'm fairly sure you'll hit the original but as well (stack overflow), but try a range or a last N and you'll see the problem with the blank query...

[v-qiuyu-msft · Accepted solution]

Hi @aarvidsson,

I can reproduce the issue on my side, and reported it internally. Will keep you updated once get any feedback.

Best Regards,
Qiuyun Yu

[aarvidsson]

Sounds excellent, @v-qiuyu-msft 🙂 I think it might actually be another bug lurking here as well; if I use table.range or any other filtering method that does not result in the stack overflow and go back to PBI Desktop, I see no data in queries despite everything looking normal in the query editor...?