Support or feedback on the preview SSIS Power Query Data Source?

[Anonymous]

Hello folks, I'd like to know how to get help and/or give feedback on the Power Query Data Source which is in preview. I tried asking my question over in the SSIS forum but was referred over here instead. 

I'm trying to use SSIS + Power Query to help me load and transform several gig's of sort-of fixed width text files that have been unceremoniously dumped on my client for analysis. It's far too much data to analyze directly from the text files, and I'm not authorized to throw the text files up into any cloud resources. Therefore I'm staging the files to on on-prem 2017 SQL Server as a single column of content in many rows, then attempting to use a Power Query Data Source query to split and cleanse the data into columns for summary analysis. 

My initial question in the SSIS forum has expanded, and now I'd also like to know:

• What can I do to better get performance or error information out of the source more quickly? I notice that when the PQ Source encounters a row that's erroring, it takes many minutes to "give up" and let the error row get passed down to the OLE DB destination & then redirected out to the flat file I'm using to catch error rows
• Is it best to "start over" if I need to change the query? I've been finding that any change to the query seems to cause mapping problems / data type problems between the source & destination, even if that change doesn't actually change the column definitions. I've recreated the source & reconfigured output columns more times than I can count, am wondering if there is a better way to do this. 
• What version of M is this Power Query Source using; would any M query I construct in Power BI desktop work in the PQ Source, or do I need to exercise caution with certain transforms? My initial efforts attempting to split using custom delimiters hasn't worked well due to occasional blank values in the columns, so I'm now trying to use Splitter.SplitTextByPositions . That's not a function exposed in the Excel version of Power Query but has been in Power BI Desktop for some time. Perhaps it's always been a part of M either way? 

[dax · Accepted solution]

Hi gmeronek,

1. If you want to get better performance or get error information, I suggest you could do this in server, the performance in Powerbi also is affected by server performance.

2. I think the  query will start over after you change query and sometimes you need to re-mapping manually to make sure successful query execution.

3. It seems that there is no version for M code. You said some M functions are different in Excel and Powerbi, I think this is by design. They all have part of functions which belong to them(such as DAX function, Not all functions are supported in all versions of Power BI Desktop, Analysis Services, and Power Pivot in Excel).

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.