Press "Enter" to skip to content

Month: April 2022

Lists in Power Query

Published 2022-04-01 by Kevin Feasel

Ed Hansberry makes a list and checks it twice:

Lists in Power Query are something many people know nothing about. Power Query uses them all the time even though you may not realize it, so if you add some List knowledge to your quiver, you’ll be able to kick your Power Query skills up a notch.

In my work, I often see the need for counting words, especially today with so much online data. Perhaps you want to ensure your Amazon product listings have a maximum number of words in the descriptions or you want to count the words in a podcast. The method I’m going to show you will count anything in your data, so you can apply this pattern to any of your datasets.

Also known as map and reduce (but not quite MapReduce).

Comments closed

Performance Gains with LAG and LEAD

Published 2022-04-01 by Kevin Feasel

Ronen Ariely provides a solution:

However, the answer in this specific case was not optimal. Unfortunately in most cases in the forums, most people that come to ask a question, do not care about learning but only about the solution, even so in my opinion the road is just as important as the end point. The road (the learning) is what will help the person to solve the next issue and not just the current one – teach a man to fish and you feed him for a lifetime…

The op marked the answer he got and I assume that from his point of view the discussion ended, but I wanted to present the solution which might be tens time better in some cases, which is what I will do in this post…. so let’s start

I won’t dive too deeply into Ronen’s philosophical argument—you can definitely read about that in the post. I will say I am sympathetic to the argument at the margin and believe it’s worthwhile to know the superior solution.

Comments closed

Error Calling SQLSetDescRec via PolyBase

Published 2022-04-01 by Kevin Feasel

Nathan Schoenack troubleshoots an error:

When trying to query an external table created for a generic ODBC external data source, the following error can be observed:

Message 7320, level 16, state 110, line 87

Unable to execute query “Remote Query” against OLE DB provider “MSOLEDBSQL” on link server “(null)”. 105082; Generic ODBC error: OdbcBufferReader.ReadBuffer, error in OdbcReadBuffer: SqlState: IM001, NativeError: 0, ‘Error calling: SQLSetDescRec(this->GetHdesc(), (SQLSMALLINT)column->idxServerCol, (SQLSMALLINT)column->odbcReadType, 0, column->valueLength, (SQLSMALLINT)column->precision, (SQLSMALLINT)column->scale, (SQLPOINTER)(pBuffer + column->valueOffset), (SQLLEN *)indPtr, (SQLLEN *)indPtr), SQL return code: -1 | SQL Error Info: Error <1>: ErrorMsg: [Microsoft][ODBC Driver Manager] The driver does not support this function. | Error calling: pReadConn->ReadBuffer(pBuffer, bufferOffset, bufferLength, pBytesRead, pRowsRead) | state: FFFF, number: 239, active connections: 9’, Connection String: Dsn={DSN Name};Driver={Driver Name};uid=root;server=xxxxx;port=xxxx;database=xxxx.

Read on for a viable workaround.

Comments closed