cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Merging tables from SQL Server and OneDrive

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.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Support
Community Support

Hi @d474boy ,

 

After my tests, check the configuration of the gateway like the solution you mentioned and then you can merge them.

SQLServer needs to be configure in the gateway. OneDrive just need to set credentials.

2-1.PNG

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

Highlighted

Hi @d474boy ,

 

If I answered your question, please mark my post as solution, this will also help others.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

4 REPLIES 4
Highlighted
Community Support
Community Support

Hi @d474boy ,

 

After my tests, check the configuration of the gateway like the solution you mentioned and then you can merge them.

SQLServer needs to be configure in the gateway. OneDrive just need to set credentials.

2-1.PNG

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

Highlighted

Thanks for the advice.

 

Our IT Dept has just changed the Gateway and it has messed everything up completely. My colleague and I now can't even publish a report from Desktop to Service. Once this is sorted out, I will try again.

 

Cheers

 

Gareth

Highlighted

Hi @d474boy ,

 

If I answered your question, please mark my post as solution, this will also help others.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

Highlighted

I am sure that this SHOULD be the solution. Unfortunately, my company's Gateway DOES have this option ticked but I still can't merge data from both Excel files (stored in SharePoint) and our SQL Server database (that has the Gateway). I have got my IT Dept. looking into it but they are confused.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors