Google Sheets Array Formulas now returning nulls

[•Daniel•]

Hi,

I use some google sheets files that I download to excels as a source of my data model,

In gSheets if you use the array syntax: Cell b1 = {a1:a3} , it overflows cell b1 to b3 with the same info as a1:a3, Until recently it worked just fine, but now powerquery only reeds the info at cell b1 but cell b2 and b3 return null despite that in the converted excel file the information displays correctly, for now I'll just stop using array syntax on the gsheets files but is there a better solution??

Thanks

 

[sandy666]

use XL2BB to post representative example from Excel and expected result

 

[viktor90]

Hi,

I use some google sheets files that I download to excels as a source of my data model,

In gSheets if you use the array syntax: Cell b1 = {a1:a3} , it overflows cell b1 to b3 with the same info as a1:a3, Until recently it worked just fine, but now powerquery only reeds the info at cell b1 but cell b2 and b3 return null despite that in the converted excel file the information displays correctly, for now I'll just stop using array syntax on the gsheets files but is there a better solution??

Thanks

Hello Daniel,
I have the same issue. Tried figuring out what was wrong inside of Power BI for ages before I came across your post. It does indeed seem like something has changed in how array formulas are handled. DId you find a solution for this yet?

Cheers,
Viktor

 

[viktor90]

I ended up replacing all array formulas by regular formulas.

 

[•Daniel•]

I ended up replacing all array formulas by regular formulas.

Hi Viktor, no solution I haven't searched more really, I actually replaced the formulas with values, they were just headers that will not change in the short term.

In the model I tried to exclusively grab data inputs in google sheets by other departments of the company, not to import any result of calculations, just the raw data. There were just a few exceptions, still if I only grab inputs I use headers to transform and clean in pquery. I'll keep this in mind to have the input data sheets as simple and clean of formulas as posible.