Log In
Register
Home
February 1, 2019
Offline
Hi Guys,
i need help with writing function to skip some manual steps while building query:
let
Source = Table.NestedJoin(TableDef, {"Key"}, TableMap, {"Key"}, "TableMap", JoinKind.LeftOuter),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", type text}, {"Col2", type text}, {"Col3", type text}, {"TableMap", type any}}),
#"Expanded TableMap" = Table.ExpandTableColumn(#"Changed Type", "TableMap", {"Col2", "Col3"}, {"TableMap.Col2", "TableMap.Col3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded TableMap",{{"TableMap.Col2", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "CustomCol3", each if Text.Contains([TableMap.Col3], "Name_") then [Col3] else [TableMap.Col3]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "CustomCol2", each if Text.Contains([TableMap.Col2], "Name_") then [Col2] else [TableMap.Col2]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"TableMap.Col2", "TableMap.Col3"}),
#"Merged Columns" = Table.CombineColumns(#"Removed Columns",{"Col2", "Col3"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"MergedDef"),
#"Inserted Merged Column" = Table.AddColumn(#"Merged Columns", "MergedMap", each Text.Combine({ [CustomCol2], [CustomCol3]}, ";"), type text),
#"Removed Columns1" = Table.RemoveColumns(#"Inserted Merged Column",{"CustomCol3", "CustomCol2"}),
#"Added Custom2" = Table.AddColumn(#"Removed Columns1", "Custom", each if [MergedDef] <> [MergedMap] then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Custom2", each true),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Custom] = 1)
in
#"Filtered Rows1"
Idea is to compare TableDefaults (TableDef) with TableMap which can be customized by user.
If one row in specific column is changed - i should show entire row as result.
So how i am building it?
1) merging queries by Key using Key Column
2) Added custom columns for all other columns to check if there is a string pattern "Name_To_Skip". If there is replace value from tableDef column into tableMap.
3) Removing not necessary columns
4) Merging columns from TableDef and TableMap and comparing if TableMap was customized.
5) If yes add custom column = 1, if not = 0
6) filter rows for only equals = 1
Imagine that i have about 30 tables to set up.
Plus each of it has about 2-15 columns to check.
What would be awesome is function which:
Check if string "Name_To_Skip" exists in expanded columns for each column and replace with equivalent in source column.
Like TableDef [Col2] and [TableMap.Col3] check them and replace adequately.
Please help,
i do not know if this is too difficult to write,
Jacek
See Answer
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
Offline
So you will have around 30 DefTables and 30 Map tables, the Maps might have that Name_To_Skip text?
I assume the corresponding tables will have the exact same column structure? (def1 and Map1 will both have 12 columns, def2 and Map2 will have 10 columns and so on)
If Map is basically a mirror of Def table but with some manual changes, there may be a way.
February 1, 2019
Offline
Hello Catalin,
yes exactly tabls have the same structure.
What can change are primary keys (i mean in example i have one key, in different table i can have 3 columns as primary key).
But these could be handled i think from seperated table. I didnt wanted to write about this yet to not want to get everything but maybe this is a good time 🙂
Best,
Jacek
February 1, 2019
Offline
Hello,
i added 2 more tables to better show you the case with primary keys:
TableName|ColumnName|KeyOrdinal
| TableDef | Key | 1 |
| TableDef | Col2 | |
| TableDef | Col3 | |
| TableMap | Key | 1 |
| TableMap | Col2 | |
| TableMap | Col3 | |
| TableDef2 | Key | 1 |
| TableDef2 | Key2 | 2 |
| TableDef2 | Value | |
| TableMap2 | Key | 1 |
| TableMap2 | Key2 | 2 |
| TableMap2 | Value |
As you can see keys can be retrived from these table.
Please help,
Jacek
February 1, 2019
Offline
Anyone ?
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
Offline
See file attached for an example.
February 1, 2019
Offline
Thank you Catalin,
this is awesome!
How can i use ListKey argument, what should i provide to trigger function?
Best,
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
Offline
This function is used in the query, you can see what arguments are sent to this function.
KeyList is a ... list.
You can put {"Key","Key2"} to send a list of 2 keys for merging.
February 1, 2019
Offline
This is awesome!
Thanks.
Ok, another question.
If i am customizing function. How do improvements/changes ? What is the best way?
You are just creating query and replacing table's names with with function's arguments?
Best,
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
Offline
Yes, I create the functions from queries, replacing the variables with function arguments.
You can convert a function back to a normal query:
let
tbl1= ConvertTable("TableDef",{"Key"}),
tbl2= ConvertTable("TableMap",{"Key"}),
Source = Table.NestedJoin(tbl1, {"Key"}, tbl2, {"Key"}, "TableMap", JoinKind.LeftOuter),
#"Added Custom" = Table.AddColumn(Source, "Custom.1", each if List.Contains([TableMap][Custom]{0}, "Name_To_Skip") then [Custom] else [TableMap][Custom]{0}),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.2", each if List.ContainsAll([Custom],[Custom.1]) then true else false),
#"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom.2] = false)),
RemovedOtherColumns = Table.SelectColumns(#"Filtered Rows",{"Key","Custom", "Custom.1"}),
Custom1 = List.Accumulate(Table.ColumnNames(RemovedOtherColumns),RemovedOtherColumns,(state,current)=>Table.TransformColumns(state, {{current, each try Text.Combine(_, ";") otherwise _}}))
in
Custom1
adding // before a line of code will disable that line, in the query above I disabled the first line that converts the query to a function and manually typed the variables.
This is now a normal query, you can see each step and change it as needed, after you finish updating the code you can convert it back to function by enabling the first line and replace the red text with function arguments.
Answers Post
February 1, 2019
Offline
Thank you very much for help!
I will do this.
Jacek
February 1, 2019
Offline
Ok Guys,
grab solution in attachment 😉
Best,
Jacek
February 1, 2019
Offline
Ok Guys,
i am returning back because i do not understand how to crate this dynamically from scratch.
I have file like in attachment.
And there is Table1:
CatDogCol2Col3
| 1 | 4 | b | c |
| 2 | 5 | e | f |
| 3 | 6 | h | i |
Cat And Dog are the keys.
And have Table13:
CatDogCol2Col3
| 1 | 4 | b | c |
| 2 | 5 | e | f |
| 3 | 6 | h | i |
Here also Cat And Dog are the keys.
What i want to do is to merge tables (inner join between Table1 and Table13) but if cointains Cat, Dog as Column Headers. If One of them is missing, just use only one column as key within inner join. So i should have the dynamic list based if TableContains pne or more keys.
I hope this is clear.
I suppose it will be building the List from table Headers which contains "Cat" and "Dog", i tried with:
let
Source = Table1,
Step2 = Table.AddColumn(List.Contains(Table.ColumnNames(Source), "Cat"), "NewOne"),
Step3 = Table.SelectColumns(Source,Step2)
in
Step3
But it is not working. What i am missing here? How to build list which cointains.
Thank you in advance.
Best,
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
Offline
Oh boy,
Step2 = Table.AddColumn(List.Contains(Table.ColumnNames(Source), "Cat"), "NewOne"),
is a mess.
List.Contains(Table.ColumnNames(Source), "Cat") will return TRUE or FALSE, if table column names contains "Cat", so the result is logical.
Table.AddColumn needs 3 arguments: a table, the name of the new column and a function that tells what to add at each row of the table.
Instead of a table, you are passing the result of List.Contains, which is not a table...
Here is how to build a list of column names matching some criteria:
February 1, 2019
Offline
Oh Thank you.
It worked! You are the best Catalin!
Few Questions:
1. Text.Contains(_,"Cat") --> what does "_" it means?
The result query:
let
Source = Table1,
Custom1 = List.Select(Table.ColumnNames(Source), each Text.Contains(_,"Cat")=true or Text.Contains(_,"Dog")=true),
#"Merged Queries" = Table.NestedJoin(Source, Custom1, Table13, Custom1, "TopoTier", JoinKind.Inner)
in
#"Merged Queries"
Hope will help somebody,
Best,
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
Offline
List.Select will analyze each item from the list.
"_" is the current item from the list.
February 1, 2019
Offline
What do you mean by current?
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
Offline
The second argument of List.Select starts an evaluation of the list, from the beginning of the list down to the last item in the list.
The _ is short hand for the current item during this process.
I suggest going more often to the documentation:
https://docs.microsoft.com/en-.....clarations
The each-expression is a syntactic shorthand for declaring untyped functions taking a single formal parameter named _ (underscore).
Simplified declarations are commonly used to improve the readability of higher-order function invocation.
For example, the following pairs of declarations are semantically equivalent:
each _ + 1 (_) => _ + 1
each [A] (_) => _[A]
Table.SelectRows( aTable, each [Weight] > 12 )
Table.SelectRows( aTable, (_) => _[Weight] > 12 )
You should look here whenever you need something, all you need is documented:
https://docs.microsoft.com/en-us/powerquery-m/power-query-m-function-reference
1 Guest(s)
