Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Reference a column in the Query Advance Editor to connect to a snowflake DB

First I am getting the Username using R, then I'm splitting the domain and the username into to columns. The next thing I want to do is to pass the domain value in column V1.1 to the snowflake connection see the bold text. I am getting this error:  

Expression.Error: The key didn't match any rows in the table.
Details:
Key=Record
Table=Table

 

Is this possible? thank you in advance for your help!

 

let
Table1 = let
RScript = R.Execute("output <- read.table(text=system2(""whoami"", stdout=TRUE))"),
UserSource = RScript{[Name="output"]}[Value],

SplitByDelimiter = (table, column, delimiter) =>
let
Count = List.Max(List.Transform(Table.Column(table, column), each List.Count(Text.Split(_,delimiter)))),
Names = List.Transform(List.Numbers(1, Count), each column & "." & Text.From(_)),
Types = List.Transform(Names, each {_, type text}),
Split = Table.SplitColumn(table, column, Splitter.SplitTextByDelimiter(delimiter), Names),
Typed = Table.TransformColumnTypes(Split, Types)
in
Typed,

#"Split Column by Delimiter" =SplitByDelimiter(UserSource, "V1", "\")
in
#"Split Column by Delimiter",
#"Uppercased Text" = Table.TransformColumns(Table1,{{"V1.1", Text.Upper, type text}}),
Source = Snowflake.Databases("xxxxxxxxxxxx.snowflakecomputing.com ", "xxxxxxxxxxxxxxxxx", []),
XXXX_Database = Source{[Name=Table.SelectRows(#"Uppercased Text", each ([V1.1])),Kind="Database"]}[Data],
PUBLIC_Schema = XXXX_Database{[Name="PUBLIC",Kind="Schema"]}[Data],
M_JOB_Table = PUBLIC_Schema{[Name="M_JOB",Kind="Table"]}[Data]

in
#"M_JOB_Table"

2 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Icon for Community Support rankCommunity Support

    Anonymous,

     

    You may change it as follows.

    = #"Uppercased Text"[V1.1]{0}
    • Anonymous's avatar
      Anonymous
      Not applicable

      It worked! thank you so much!