Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
In one of the systems on which we have been asked to report, a new copy of the database (SQLServer) is initiated each year (Not my design). This means to report over several years, we have to merge data from several databases into a single reporting table.
Traditionally, this would have been achieved by creating a separate query for each database and merging the resulting query results. Unfortunately this means the PowerQuery scripts would have to be edited each year to account for the new database.
I tried to make this more dynamic by obtaining a list of the required databases via a Sql.Dtaabases(Server) call and then adding a new column whose definition included .... each Sql.Database(Server,[Database Name],Query="....."). As the databases have the same structure, the same SQL queries could be used.
This worked extremely well in the desktop, however when I published the report and attempted to configure the dataset refresh, it reported that the query "contains an unsupported function. That function is Sql.Database"
Any idea why? It is supported in the 'Source =' context.
Solved! Go to Solution.
Hi @Mixednuts ,
Adding data source in Gateway need address and database name, we can expand the result of Sql.Databases() instead of connect them again.
1. Expand the data column of Sql.Databases()
2. Filter the tables you want.
3. expand the data of tables after remove unuseful columns
But currently the On-Premises Data Gateway (Standard Mode) does not support blank database name.
But we can use On-Premises Data Gateway (Personal Mode) as a workaround.
All the queries are here for reference:
let
Source = Sql.Databases("localhost"),
#"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Name", "Data", "Schema", "Item", "Kind"}, {"Data.Name", "Data.Data", "Data.Schema", "Data.Item", "Data.Kind"}),
#"Filtered Rows2" = Table.SelectRows(#"Expanded Data", each ([Data.Name] = "845087" or [Data.Name] = "863829")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows2",{"Data.Schema", "Data.Item", "Data.Kind", "Kind"}),
#"Expanded Data.Data" = Table.ExpandTableColumn(#"Removed Columns", "Data.Data", {"AccountNumber", "CurrencyCode", "id", "DateTime"}, {"Data.Data.AccountNumber", "Data.Data.CurrencyCode", "Data.Data.id", "Data.Data.DateTime"})
in
#"Expanded Data.Data"
Best regards,
Hi @Mixednuts ,
Adding data source in Gateway need address and database name, we can expand the result of Sql.Databases() instead of connect them again.
1. Expand the data column of Sql.Databases()
2. Filter the tables you want.
3. expand the data of tables after remove unuseful columns
But currently the On-Premises Data Gateway (Standard Mode) does not support blank database name.
But we can use On-Premises Data Gateway (Personal Mode) as a workaround.
All the queries are here for reference:
let
Source = Sql.Databases("localhost"),
#"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Name", "Data", "Schema", "Item", "Kind"}, {"Data.Name", "Data.Data", "Data.Schema", "Data.Item", "Data.Kind"}),
#"Filtered Rows2" = Table.SelectRows(#"Expanded Data", each ([Data.Name] = "845087" or [Data.Name] = "863829")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows2",{"Data.Schema", "Data.Item", "Data.Kind", "Kind"}),
#"Expanded Data.Data" = Table.ExpandTableColumn(#"Removed Columns", "Data.Data", {"AccountNumber", "CurrencyCode", "id", "DateTime"}, {"Data.Data.AccountNumber", "Data.Data.CurrencyCode", "Data.Data.id", "Data.Data.DateTime"})
in
#"Expanded Data.Data"
Best regards,
Thank you Dong Li.
Appreciate the effort you put into this!
In summary then, the work around is to use a personal gateway rather than a Standard Gateway?
Is this considered something to be fixed from a Standard Gateway perspective? We don't want to have to permanently operate via personal gateway if at all possible....
Hi @Mixednuts ,
We get a solution to create a database without database name by using RESTful API.
1. we encode the credential with C# refer to this document:https://docs.microsoft.com/en-us/power-bi/developer/encrypt-credentials
using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;
namespace ConsoleApp2
{
class Program
{
static void Main(string[] args)
{
var credentials = "{\"credentialData\":[{\"name\":\"username\", \"value\":\"UserName\"},{\"name\":\"password\", \"value\":\"Password\"}]}";
var result = AsymmetricKeyEncryptionHelper.EncodeCredentials(credentials, "Place-Yuor-Exponent-Here", "Place-Yuor-Modulus-Here");
Console.WriteLine(result);
Console.ReadLine();
}
}
public static class AsymmetricKeyEncryptionHelper
{
private const int SegmentLength = 85;
private const int EncryptedLength = 128;
public static string EncodeCredentials(string credentials, string publicKeyExponent, string publicKeyModulus)
{
using (RSACryptoServiceProvider rsa = new RSACryptoServiceProvider(EncryptedLength * 8))
{
var parameters = rsa.ExportParameters(false);
parameters.Exponent = Convert.FromBase64String(publicKeyExponent);
parameters.Modulus = Convert.FromBase64String(publicKeyModulus);
rsa.ImportParameters(parameters);
return Encrypt(credentials, rsa);
}
}
private static string Encrypt(string plainText, RSACryptoServiceProvider rsa)
{
byte[] plainTextArray = Encoding.UTF8.GetBytes(plainText);
// Split the message into different segments, each segment's length is 85. So the result may be 85,85,85,20.
bool hasIncompleteSegment = plainTextArray.Length % SegmentLength != 0;
int segmentNumber = (!hasIncompleteSegment) ? (plainTextArray.Length / SegmentLength) : ((plainTextArray.Length / SegmentLength) + 1);
byte[] encryptedData = new byte[segmentNumber * EncryptedLength];
int encryptedDataPosition = 0;
for (var i = 0; i < segmentNumber; i++)
{
int lengthToCopy;
if (i == segmentNumber - 1 && hasIncompleteSegment)
lengthToCopy = plainTextArray.Length % SegmentLength;
else
lengthToCopy = SegmentLength;
var segment = new byte[lengthToCopy];
Array.Copy(plainTextArray, i * SegmentLength, segment, 0, lengthToCopy);
var segmentEncryptedResult = rsa.Encrypt(segment, true);
Array.Copy(segmentEncryptedResult, 0, encryptedData, encryptedDataPosition, segmentEncryptedResult.Length);
encryptedDataPosition += segmentEncryptedResult.Length;
}
return Convert.ToBase64String(encryptedData);
}
}
}
The Exponent and Modulus can be find with the Get Database API
2. then we can use the Create Data Source API to create it, the body is like following:
{
"dataSourceType": "SQL",
"connectionDetails": "{\"server\":\"localhost\",\"database\":\"\"}",
"datasourceName": "Test100",
"credentialDetails": {
"credentialType": "Basic",
"credentials": "Generated-Credentials",
"encryptedConnection": "Encrypted",
"encryptionAlgorithm": "RSA-OAEP",
"privacyLevel": "None"
}
}
But the main point is expanding the result of Sql.Databases() instead of create another column using Sql.Database().
Best regards,
Thank you again, Dong Li.
I understand that I should not use the function in an Add Custom Column scenario.
So, to get support through a Standard gateway, I will need to create a Custom Connector as per your instructions.
regards,
John
Hi @Mixednuts ,
Sorry for did not explained clearly, we did not create a custom connector, we are using RESTful API to add data source as same as adding data source in "Manage Gateway" because we found we cannot add a data source without name of database in "Manage Gateway". The C# application is to generate the credential that used to add the data source, you can create it in local using Visual Studio or another IDE, then you can just use the "Try it " in API document to call the API.
Best regards,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.