February 1, 2019
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
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
Answers Post
1 Guest(s)