Hi
I wrote up my question before looking on the community forum.
I think the solution might be the same as for https://community.powerbi.com/t5/Desktop/Merging-tables-from-two-different-data-sources/m-p/679324#M... however, I am not in IT so cannot check the configuration of the gateway. I have asked someone in IT to investigate, but in the meantime, could anyone see any OTHER reason why what I am trying does not work.
1.1.1. Question for the Power BI Pro Users Community
I want to be able to merge three tables (‘qryNext2DaysDates’; ‘qryAreas’ and ‘qryJobTypeGroups’) together to create a cartesian product of all their values.
1.1.1.1. qryNext2DaysDates
Automatically generated list of dates. I have limited it to 2 days in this test model but am really using 28 days from the Monday of the current week.
= List.Dates(DateTime.Date(Date.StartOfWeek(DateTime.LocalNow())), 2, #duration(1, 0, 0, 0))
Converted to a table and field renamed to end up with a very simple table:
DateKey |
06/01/2020 |
07/01/2020 |
1.1.1.2. qryAreas
This table is obtained from all values of the CDEPOT_C field that exist in the SQL Server table ‘..._SWOR’ in the ...GCC database.
I have cleaned them up to only keep North, South, East and West. Anything else is mapped to Other.
Then I have added numbers to the start.
The resultant table is:
Area |
1. North |
2. South |
3. East |
4. West |
5. Other |
1.1.1.3. qryJobTypeGroups
The source of this table is an Excel spreadsheet table stored on my OneDrive.
The original table is a list of all Defect Job Types with each mapped to a ‘Group’.
The resultant table is:
JobTypeGroup |
1. Black |
2. White |
3. Green |
4. Road Markings |
5. VRS |
6. Other |
1.1.1.4. qryDateAreaJtg query
The table that I am trying to create is very simple in Power BI Desktop. The full thing shown below:
let
Source = qryNext2DaysDates,
#"Added Custom" = Table.AddColumn(Source, "Area", each qryAreas),
#"Expanded Area" = Table.ExpandTableColumn(#"Added Custom", "Area", {"Area"}, {"Area.Area"}),
#"Added Custom1" = Table.AddColumn(#"Expanded Area", "JobTypeGroup", each qryJobTypeGroups),
#"Expanded JobTypeGroup" = Table.ExpandTableColumn(#"Added Custom1", "JobTypeGroup", {"JobTypeGroup"}, {"JobTypeGroup.JobTypeGroup"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded JobTypeGroup",{{"Next4WeeksDates", "DateKey"}, {"Area.Area", "Area"}, {"JobTypeGroup.JobTypeGroup", "JobTypeGroup"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"DateKey", Order.Ascending}, {"Area", Order.Ascending}, {"JobTypeGroup", Order.Ascending}})
in
#"Sorted Rows"
The final table that I want is:
DateKey | Area | JobTypeGroup |
06/01/2020 | 1. North | 1. Black |
06/01/2020 | 1. North | 2. White |
06/01/2020 | 1. North | 3. Green |
06/01/2020 | 1. North | 4. Road Markings |
06/01/2020 | 1. North | 5. VRS |
06/01/2020 | 1. North | 6. Other |
06/01/2020 | 2. South | 1. Black |
06/01/2020 | 2. South | 2. White |
06/01/2020 | 2. South | 3. Green |
06/01/2020 | 2. South | 4. Road Markings |
06/01/2020 | 2. South | 5. VRS |
06/01/2020 | 2. South | 6. Other |
06/01/2020 | 3. East | 1. Black |
06/01/2020 | 3. East | 2. White |
06/01/2020 | 3. East | 3. Green |
06/01/2020 | 3. East | 4. Road Markings |
06/01/2020 | 3. East | 5. VRS |
06/01/2020 | 3. East | 6. Other |
06/01/2020 | 4. West | 1. Black |
06/01/2020 | 4. West | 2. White |
06/01/2020 | 4. West | 3. Green |
06/01/2020 | 4. West | 4. Road Markings |
06/01/2020 | 4. West | 5. VRS |
06/01/2020 | 4. West | 6. Other |
06/01/2020 | 5. Other | 1. Black |
06/01/2020 | 5. Other | 2. White |
06/01/2020 | 5. Other | 3. Green |
06/01/2020 | 5. Other | 4. Road Markings |
06/01/2020 | 5. Other | 5. VRS |
06/01/2020 | 5. Other | 6. Other |
07/01/2020 | 1. North | 1. Black |
07/01/2020 | 1. North | 2. White |
07/01/2020 | 1. North | 3. Green |
07/01/2020 | 1. North | 4. Road Markings |
07/01/2020 | 1. North | 5. VRS |
07/01/2020 | 1. North | 6. Other |
07/01/2020 | 2. South | 1. Black |
07/01/2020 | 2. South | 2. White |
07/01/2020 | 2. South | 3. Green |
07/01/2020 | 2. South | 4. Road Markings |
07/01/2020 | 2. South | 5. VRS |
07/01/2020 | 2. South | 6. Other |
07/01/2020 | 3. East | 1. Black |
07/01/2020 | 3. East | 2. White |
07/01/2020 | 3. East | 3. Green |
07/01/2020 | 3. East | 4. Road Markings |
07/01/2020 | 3. East | 5. VRS |
07/01/2020 | 3. East | 6. Other |
07/01/2020 | 4. West | 1. Black |
07/01/2020 | 4. West | 2. White |
07/01/2020 | 4. West | 3. Green |
07/01/2020 | 4. West | 4. Road Markings |
07/01/2020 | 4. West | 5. VRS |
07/01/2020 | 4. West | 6. Other |
07/01/2020 | 5. Other | 1. Black |
07/01/2020 | 5. Other | 2. White |
07/01/2020 | 5. Other | 3. Green |
07/01/2020 | 5. Other | 4. Road Markings |
07/01/2020 | 5. Other | 5. VRS |
07/01/2020 | 5. Other | 6. Other |
I then go on to do other things with this table.
This is just a fundamental thing that we might want to do, i.e. merge tables from Excel files stored on OneDrive or SharePoint Online with the tables from SQL Server.
1.1.1.5. But it WON’T work in Power BI Service.
The error message looks like the following:
Something went wrong
Unable to connect to the data source undefined.
Please try again later or contact support. If you contact support, please provide these details.
Underlying error code: -2147467259 Table: qryDateAreaJtg.
Underlying error message: SharePoint: Request failed: https://vincieurovia-my.sharepoint.com/personal/ghodge_kherty_co_uk/Documents/Gareth/Defects/Defects Report tables for Power BI_xlsx/_api/contextinfo
DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259
Microsoft.Data.Mashup.ValueError.Reason: DataSource.Error
Cluster URI: WABI-NORTH-EUROPE-redirect.analysis.windows.net
Activity ID: cbf0bf4f-ea32-479a-9945-8387a1fd967e
Request ID: 1ecdd841-91ac-0ffb-e242-805b142462c9
Time: 2020-01-06 11:46:39Z
1.1.1.6. I’ve tried all sorts to make it work
Tried creating a dummy field with the same value on all rows. Could then use a Table.NestedJoin function as opposed to the Table.ExpandTableColumn. Didn’t help.
Tried Left outer Join as well as Full Outer Join. Didn’t help.
Tried removing all the relationships between all of the tables. Didn’t help.
The problem definitely seems due to the fact that the first merge of Date and Areas table retains some sort of link to the SQL Server source. Then when add in the JobTypeGroup table, it needs to refer to both OneDrive and SQL Server. And it doesn’t like it.
As I say above, I hope that the solution is just for IT to check the box to allow our SQL Gateway to allow cloud sources to refresh through the gateway.
