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"
