query in query (Dynamic database name)

[Kibosh]

Hey all,

I need to read out some values from SQL-tables generated bij the SCADA program Siemens WINCC.

But that SCADA has a very anoying thing: Each time the project is "compiled and loaded" it changes it's runtime database name with a time-stamp. And it's giving me issues to get it working with Power BI to make reports. I already asked Siemens if there was a way it would not do that, but no luck.

The runtime database will always start with "CC_HMI_xxxx" and ends on "xxxxx_xxxR".

For example: "CC_HMI_7HI6_19_10_21_14_29_48R"

I have query's working but still running into issues.

Query 1 ("ArchiveTags"):

let
   
    Source = Sql.Databases("SERVER"),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "CC_HMI_") and Text.EndsWith([Name], "R")),
    Name = #"Filtered Rows"[Name],
    dbName = Text.Trim(Lines.ToText(List.FirstN(Name,1))),
    
    Source2 = Sql.Database("SERVER\WINCC", dbName),
    dbo_ArchiveTags = Source2{[Schema="dbo",Item="Archive"]}[Data]
in
    dbo_ArchiveTags

The above query runs ok but when powerbi is started, it asks for the credentials. It adds the old database name which it last accessed. So it is trying to connect to a db that no longer exist. Is there a way to add the credentials inside the query?

Query 2 ("ArchiveValues"):

let
   
    Source3 = Sql.Databases("SERVER\WINCC"),
    #"Filtered Rows" = Table.SelectRows(Source3, each Text.StartsWith([Name], "CC_HMI_") and Text.EndsWith([Name], "R")),
    Name = #"Filtered Rows"[Name],
    dbName = Text.Trim(Lines.ToText(List.FirstN(Name,1))),

    Source4 = OleDb.DataSource("provider=WinCCOLEDBProvider.1;data source=SERVER\WINCC;catalog=" & dbName & ";mode=Read", [Query="TAG:R,(1;2;3;4),'0000-00-00 00:30:00.000','0000-00-00 00:00:00.000'"])
in
    Source4

Second query uses a different provider from Siemens itself to read out the actual tag values. The archived values are segmented and compressed so I have to use the "WinCCOLEDBProvider". 

But when I do "apply changes" I get the error "Sequence contains more than one element"?

Someone has a thought how to proceed?

[artemus]

Unfortunatly the only way to retrieve credentials (and reuse them) for a conection is to write your own Power Bi connector. These are not supported in the online version.

You could also set up a web server which forwards the requests.

[Kibosh]

Thx for the reply,

Sounds to complex for me...