Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help with DW Schema queries

I'm working with multiple servers which have the same database, so I have to combine them. Sometimes they stop working, so I set parametes (ACT_Source...) to indicate if they are online or not.

The first query is currently working. I would like to change the second query (which has a different pattern) to have the same 

I have two types of query and I would like to let them at the same patern.

 

let
    fxConnection =
        (server as text) as table =>
            Table.AddColumn(
                Sql.Database(server, Database, [Query="SELECT... "]),
                "Server",
                each server
            ),
    #"CombinedTables" =
        Table.Combine(List.RemoveNulls(
            {
                if ACT_SourceARA = 1 then
                    fxConnection(ARA)
                else
                    null,
                if ACT_SourceGOI = 1 then
                    fxConnection(GOI)
                else
                    null,
                if ACT_SourceIBI = 1 then
                    fxConnection(IBI)
                else
                    null
            })
        )
in
    #"CombinedTables"

 

This is second query:

 

let
    Source = Sql.Database(server, Database),
    DW_DEClass = Source{[Schema="DW",Item="DEClass"]}[Data]
in
    DW_DEClass

 

I think it should looks like the query bellow. I tried add lines 2 and 3 from second query into the first query, but I don't know exactly what should be changed to make this work.

 

let
    fxConnection =
        (server as text) as table =>
            Table.AddColumn(

              ( Source = Sql.Database(server, Database),
                DW_DEClass = Source{[Schema="DW",Item="DEClass"]}[Data] )

                "Server",
                each server
            ),
    #"CombinedTables" =
        Table.Combine(List.RemoveNulls(
            {
                if ACT_SourceARA = 1 then
                    fxConnection(ARA)
                else
                    null,
                if ACT_SourceGOI = 1 then
                    fxConnection(GOI)
                else
                    null,
                if ACT_SourceIBI = 1 then
                    fxConnection(IBI)
                else
                    null
            })
        )
in
    #"CombinedTables"​

 

I appreciate any help!

 

  • Use this (I can't see you passing Database here. You might need to replace Database with something like "Database" here depending upon your database name)

     

    let
        fxConnection =
            (server as text) as table =>
                Table.AddColumn(
                    Sql.Database(server, Database){[Schema="DW",Item="DEClass"]}[Data],
                    "Server",
                    each server
                ),
        #"CombinedTables" =
            Table.Combine(List.RemoveNulls(
                {
                    if ACT_SourceARA = 1 then
                        fxConnection(ARA)
                    else
                        null,
                    if ACT_SourceGOI = 1 then
                        fxConnection(GOI)
                    else
                        null,
                    if ACT_SourceIBI = 1 then
                        fxConnection(IBI)
                    else
                        null
                })
            )
    in
        #"CombinedTables"

     

2 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional

    Use this (I can't see you passing Database here. You might need to replace Database with something like "Database" here depending upon your database name)

     

    let
        fxConnection =
            (server as text) as table =>
                Table.AddColumn(
                    Sql.Database(server, Database){[Schema="DW",Item="DEClass"]}[Data],
                    "Server",
                    each server
                ),
        #"CombinedTables" =
            Table.Combine(List.RemoveNulls(
                {
                    if ACT_SourceARA = 1 then
                        fxConnection(ARA)
                    else
                        null,
                    if ACT_SourceGOI = 1 then
                        fxConnection(GOI)
                    else
                        null,
                    if ACT_SourceIBI = 1 then
                        fxConnection(IBI)
                    else
                        null
                })
            )
    in
        #"CombinedTables"

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thank you again @Vijay_A_Verma!!

      You have saved me twice today 😄