Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I'm having the following issue:
Data source error: {"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"The column '31/1/2022' of the table wasn't found."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Detail","detail":{"type":1,"value":"31/1/2022"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"Expression.Error"}}],"exceptionCulprit":1}}} Table: Revenue.
Cluster URI: WABI-WEST-US-B-PRIMARY-redirect.analysis.windows.net
Activity ID: be11d23d-ac7f-4c3f-bcba-e2ccec6155ec
Request ID: cc0896fd-fb27-4cd1-8ffc-d24971d5e3c2
Time: 2022-09-28 15:21:25Z
The table is a merge between 2 excel sheets and then appending 3 tables coming from 3 MySQL queries.
When I see this The column '31/1/2022' in the error message it makes me think on the excel sheets: the headers from excels sheets are dates and one of them is exactly this, 31/1/2022. It is strange to me because I've worked with this kind of headers previously and never have this error. Any thoughts?
I've checked all the posts related and I've already:
Restarted the Gateway
Set the Privacy level to Public
Solved! Go to Solution.
I found the "issue".
The excel file data was coming from a unique sheet having dates as headers for different topics. In this case, the revenue headers were the same as sales headers (month dates for 2022). I think this was hard to process for PBI because it writes the first 12 columns as 31/1/2022 - 28/2/2022 - ... - 31/12/2022 but the next 12 columns as 31/1/2022_<numberX> - 28/2/2022_<numberY> - ... - 31/12/2022_<numberZ>. I need to delete those numbers (logically) but it seems the server do not read nor create those numbers. As you say, creating a dataflow this might be solved.
Thanks!
Marc
Hi @Anonymous - it is difficult to help without complete knowledge of the Excel files and MySQL tables. Would you please consider opening a support ticket so Microsoft can help investigate.
If I was to try something, I would explore importing the different sources into separate table in a Dataflow, and then merge the tables from the Dataflows. This would allow you to revert to the "Organisation" privacy setting.
The message Column "31/1/2022" of the table would suggest that need to unpivot columns to rows somewhere, and the "31/1/2022" was hard coded instead of dynamically referenced.
I found the "issue".
The excel file data was coming from a unique sheet having dates as headers for different topics. In this case, the revenue headers were the same as sales headers (month dates for 2022). I think this was hard to process for PBI because it writes the first 12 columns as 31/1/2022 - 28/2/2022 - ... - 31/12/2022 but the next 12 columns as 31/1/2022_<numberX> - 28/2/2022_<numberY> - ... - 31/12/2022_<numberZ>. I need to delete those numbers (logically) but it seems the server do not read nor create those numbers. As you say, creating a dataflow this might be solved.
Thanks!
Marc