Hi Guys,
i have the function which retriving PrimaryKeys From table "TableKeys" :
(TableName)=>
let
Source = Excel.CurrentWorkbook(){[Name="TableKeys"]}[Content],
#"Changed Type" = List.Accumulate(Table.ColumnNames(Source), Source, (state, current)=> Table.TransformColumnTypes(state,{{current, type text}})),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([KeyOrdinal] <> null)),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"TableName"}, {{"Key", each _, type table [TableName=nullable text, ColumnName=nullable text, KeyOrdinal=nullable text]}}),
#"List" = Table.AddColumn(#"Grouped Rows", "Keys", each Table.Column([Key],"ColumnName")),
#"Filter" = Table.SelectRows(#"List", each ([TableName] = TableName))
in
#"Filter"
And working nice when argument = "Table" (exists in "TableKeys").
Problem is that i want to Input as Argument "TableDef". If "TableDef" exists in "TableKeys" - functions works.
But when "TableDef" does not exists in "TableKeys" i am getting error.
What i want to do is to check If there is just TableName without "Def" suffix. So in this case "Table" will have match ( Text.Start(TableName,Text.Length(TableName)-3)).
If "Table" is present - use table "Table" name instead of "TableDef". Because in this case "TableDef" and "Table" are equal and have the same primary keys.
How can i get this?
I tried with adding #"TableName Replace" step but i failed:
(TableName)=>
let
Source = Excel.CurrentWorkbook(){[Name="TableKeys"]}[Content],
#"Changed Type" = List.Accumulate(Table.ColumnNames(Source), Source, (state, current)=> Table.TransformColumnTypes(state,{{current, type text}})),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([KeyOrdinal] <> null)),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"TableName"}, {{"Key", each _, type table [TableName=nullable text, ColumnName=nullable text, KeyOrdinal=nullable text]}}),
#"List" = Table.AddColumn(#"Grouped Rows", "Keys", each Table.Column([Key],"ColumnName")),
#"TableName Replace" = if Text.Contains([TableType],TableName) = true then TableName = TableName else TableName = Text.Start(TableName,Text.Length(TableName)-3),
#"Filter" = Table.SelectRows(#"List", each ([TableName] = TableName))
in
#"Filter"
Please help,
Jacek
In this case, Source is the TableKeys:
Source = Excel.CurrentWorkbook(){[Name="TableKeys"]}[Content],
If you refer to Source[TableName] column, this is a list (any column type is list type), therefore you can use list functions on a column:
If List.Contains(Source[TableName], "Table") then ... do whatever you need
Thank you!