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

Applying a dynamic column prefix with SQL source table name

Hi 

I've found this article from Chris Webb in an effort to apply dynamic prefix's to column names with an SQL query.

https://blog.crossjoin.co.uk/2014/09/29/advanced-options-for-loading-data-from-sql-server-with-power...

 

I want to add a dynamic value to the a prefix of columns as they are loaded from SQL.

 

I have the below query

 

let
Source = Sql.Database("GOULBDB42", "PALMSDB"),
#"Navigation 1" = Source{[Schema = "dbo", Item = "tblActivityGroup"]}[Data],
Custom = Table.PrefixColumns(#"Navigation 1", "PALMS_STG_tblActivityGroup") as table
in
Custom

what I want to achieve is a parameter driven variable for "PALMS_STG_tblActivityGroup" based on the value of Item in the Source function prior with a concatenated string.

 

Trying to adapt Chris's code  my puesdo code is

 

let
//declare a function that combines a list of text using commas
ConcatByComma = Combiner.CombineTextByDelimiter(","),

//declare a name function
MyNameFunction = (p, a) => 
List.Transform(a, each 
"Navigate from " & _[SourceTableName] & " to " & _[TargetTableName] & 
" via " & ConcatByComma(_[SourceKeys]) & " and " & ConcatByComma(_[TargetKeys])),

//use this name function in Sql.Database
Source = Sql.Database("GOULBDB42", "PALMSDB", [NavigationPropertyNameGenerator=MyNameFunction,CreateNavigationProperties=false] ),
dbo_DimDate = Source{[Schema = "dbo", Item = "tblActivityGroup"]}[Data],

// set a parameter value for an ETL prefix to "PALMS_STG_"

prefixStg = "PALMS_STG_"

// declare a function to get the dynamic name of the SQL table from the Source function (or elsewhere ??)

TableName = Item //or Name = _[SourceTableName]

Applyprefix = Table.PrefixColumns(dbo_DimDate , prefixStg&TableName) as table
in
Applyprefix


Does anyone have a function to a similar use case or a solution to the above ?

 

I've tried a few ways of accessing the _[SourceTableName] parameter from the function but I don't know enough of it's context to debug. The error tells me it can't convert a table type to text type. But I think I'm further from the solution than a type error.....

 

I need to access the value and set a separate parameter from the each statement _[SourceTableName] from the MyNameFunction but I don't know how.

Any help or another method to acheive this would be much appreciated 

Simon

2 REPLIES 2
Anonymous
Not applicable

Hi @Anonymous ,

 

as per Chris' blog - this is not very well documented function and it is hard to tell what is going wrong without seeing what is been passed to the function as (p, a) parameters ni your case. Based on what error you get _[SourceTableName] refers to a table, not to a text value in the field. It is possible to drill into this parameter to reconstruct the functionality that is demonstrated in Chris' blog, but it will take quite a bit of effort.

 

If the @g_datasense or the below solution works, I, personally, suggest to go this way :).

 

This is another option using related lists:

let

ListOfTables=
{
    {"PALMSDB"                    , "ANOTHERDB"},
    {"PALMS_STG_tblActivityGroup", "ANOTEHR_DB_tblActivityGroup"}
},

pTableName = "PALMSDB",
mPrefixNmae = ListOfTables{1}{List.PositionOf(ListOfTables{0}, pTableName)},

Source = Sql.Database("GOULBDB42", pTableName),
#"Navigation 1" = Source{[Schema = "dbo", Item = "tblActivityGroup"]}[Data],
Custom = Table.PrefixColumns(#"Navigation 1", mPrefixNmae) as table
in
Custom

 

g_datasense
Advocate I
Advocate I

Hello @Anonymous ,

 

You can do this in another sense, in my case for example, I've created a "variable" where I put my SQL query and call my parameter concatenated and then another query to call this variable.

 

Concatenate SQL Query and Parameter (ConCat)

let
Source =
" SELECT ID_SALE,
SERIAL_NUMBER,
CODE_CURRENCY,
CURRENCY,
LOCATION

FROM SALE

WHERE
SERIAL_NUMBER IN ('" & Text.Combine(#"SN List", "', '")&"') "

in
Source

 

Call your SQL Query

let
Sales = Sql.Database("GOULBDB42", "PALMSDB", [Query= ConCat ])
in
Sales

  

Thanks,

Guilherme Antunes

https://www.linkedin.com/in/antunesguilherme 

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