Applying a dynamic column prefix with SQL source table name

[Anonymous]

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

[Anonymous]

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]

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