Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Strange Gateway issue

Earlier this week we upgraded our gateway to the latest version.  This morning a user contacted me stating that they could no longer select the gateway for an existing report that had previously been working fine with not changes made.

 

I've narrowed the problem down to a query that simply combines data from 3 other queries into a single table.  Removing this combining query resolves the issue (code below).  Does anyone know what about this query would cause the gateway to disallow access (error message below)?

Capture.PNG

let
    Source = #"LYNDA user list",
    #"Appended Query" = Table.Combine({#"Source", #"LYNDA historic data"}),
    #"Replaced Value10" = Table.ReplaceValue(#"Appended Query","M","Male",Replacer.ReplaceText,{"Gender"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value10","F","Female",Replacer.ReplaceText,{"Gender"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value1","","Not specified",Replacer.ReplaceValue,{"Gender"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value3", "Full name", each [First name] & " " & [Last name]),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Upload Date", "Profile Creation Date"}, {"Total hours", "Hours trained"}}),
    #"Replaced Value7" = Table.ReplaceValue(#"Renamed Columns","OD","Support Services",Replacer.ReplaceText,{"Business Stream"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value7",{{"Profile Creation Date", type datetime}, {"Registration Date", type datetime}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Last log in", "Last login Date"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Hours trained", type number}, {"Logins", Int64.Type}, {"Profile Creation Date", type date}, {"Last login Date", type datetime}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type2",{"Last login Date", "User ID", "Registration Date"},#"LYNDA last login date",{"Login date", "User ID", "Registration date"},"NewColumn",JoinKind.Inner),
    #"Changed Type3" = Table.TransformColumnTypes(#"Merged Queries",{{"Hours viewed/log in", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type3",{"NewColumn"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Removed Columns",{"Group2"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns2", "User ID")
in
    #"Removed Duplicates"
1 ACCEPTED SOLUTION
Anonymous
Not applicable

Here is how the issue occurs:

 

Have multiple CSV files in a folder.  Link your gateway to the folder.  At this point everything is happy.

 

Create a new query that does a merge on 2 or more of those CSV files.  Now gateway is unhappy.

 

Update gateway to link to each file directly, gateway is happy again.

 

Its not a solution, but its a work around for right now.

View solution in original post

9 REPLIES 9

I'm having the same situation.

 

Please Microsoft solve this ASAP

Anonymous
Not applicable

I am having also same problems. Extremely frustrating! I cannot make reports in my organization!

 

My thread:
https://community.powerbi.com/t5/Service/Append-problem-Azure-CosmosDB-query-Static-XLS-file-No-sche...

Anonymous
Not applicable

Looks like another thread is displaying a similar issue:

https://community.powerbi.com/t5/Service/Gateway-disabled/m-p/268213#M32875

@Anonymous,

I am not able to reproduce this issue when I perform a merge query operation in my PBIX file.  Could you please share me your PBIX file so that I can test?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Here is how the issue occurs:

 

Have multiple CSV files in a folder.  Link your gateway to the folder.  At this point everything is happy.

 

Create a new query that does a merge on 2 or more of those CSV files.  Now gateway is unhappy.

 

Update gateway to link to each file directly, gateway is happy again.

 

Its not a solution, but its a work around for right now.

Anonymous
Not applicable

Hi @Anonymous

The problem I have is that when setting the data source for the specific file, I cannot as my files is saved in a SharePoint Site Folder, not a Windows Folder. So it does not allow me to set the file path with a "http://" type of path.

Also, I have not upgraded the gateway since last year. Would that fix the problme if I do now?

Thanks for your help.

Anonymous
Not applicable

I've not personally tested a sharepoint source for this issue, so i'm unsure if the merge issue is present.

 

Our gateway is a few versions out of date, i'm looking to upgrade myself soon.  Just balancing the availablity of my infrastructure team.

 

Sorry I cannot provide much further insight.

Anybody still experiencing this issue?

 

I have a bunch of web data that cannot auto-refresh because it's appended into a single dataset.

We ran into this same issue with a Windows 10 upgrade on the machine that houses our enterprise gateway this week. Prior to the upgrade, our organization used multiple models connected to various folders with CSV files in them and then merged them together in queries. We were able to schedule refreshes from these locations without a problem After the windows update, the gateway would not show as available for these models that contained merged queries.

 

Each indivisual source folder would be available to schedule refreshes in the gateway.

Each individual file would be available to schedule refreshes in the gateway.  

But when a file was used in a merged query, the only way to have the gateway available to schedule refreshes was to directly link to each source file as a different connection in the gateway.

 

Please correct this! Our gateway is now filled with connections to individual files rather than the containing folders or sharepoint locations.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors