• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

Automating repeating steps with Power Query function|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Automating repeating steps with Power Query function|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryAutomating repeating steps with Pow…
sp_PrintTopic sp_TopicIcon
Automating repeating steps with Power Query function
Avatar
jaryszek
Member
Members
Level 0
Forum Posts: 183
Member Since:
February 1, 2019
sp_UserOfflineSmall Offline
1
May 22, 2021 - 12:33 am
sp_Permalink sp_Print sp_EditHistory

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

sp_AnswersTopicSeeAnswer See Answer
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1845
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
May 23, 2021 - 3:51 pm
sp_Permalink sp_Print

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.

Avatar
jaryszek
Member
Members
Level 0
Forum Posts: 183
Member Since:
February 1, 2019
sp_UserOfflineSmall Offline
3
May 23, 2021 - 8:53 pm
sp_Permalink sp_Print

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

Avatar
jaryszek
Member
Members
Level 0
Forum Posts: 183
Member Since:
February 1, 2019
sp_UserOfflineSmall Offline
4
May 24, 2021 - 5:59 pm
sp_Permalink sp_Print sp_EditHistory

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

Avatar
jaryszek
Member
Members
Level 0
Forum Posts: 183
Member Since:
February 1, 2019
sp_UserOfflineSmall Offline
5
May 25, 2021 - 2:20 pm
sp_Permalink sp_Print

Anyone ?

Jacek

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1845
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
6
May 26, 2021 - 4:28 pm
sp_Permalink sp_Print

See file attached for an example.

Avatar
jaryszek
Member
Members
Level 0
Forum Posts: 183
Member Since:
February 1, 2019
sp_UserOfflineSmall Offline
7
May 26, 2021 - 8:56 pm
sp_Permalink sp_Print

Thank you Catalin,

this is awesome!

How can i use ListKey argument, what should i provide to trigger function?

Screenshot_173.pngImage Enlarger

Best,
Jacek

sp_PlupAttachments Attachments
  • sp_PlupImage Screenshot_173.png (211 KB)
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1845
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
8
May 26, 2021 - 9:07 pm
sp_Permalink sp_Print

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.

Avatar
jaryszek
Member
Members
Level 0
Forum Posts: 183
Member Since:
February 1, 2019
sp_UserOfflineSmall Offline
9
May 27, 2021 - 5:36 pm
sp_Permalink sp_Print sp_EditHistory

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1845
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
10
May 27, 2021 - 7:42 pm
sp_Permalink sp_Print sp_EditHistory

Yes, I create the functions from queries, replacing the variables with function arguments.

You can convert a function back to a normal query:

// (TableName1,TableName2,KeyList)=>
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.

sp_AnswersTopicAnswer
Answers Post
Avatar
jaryszek
Member
Members
Level 0
Forum Posts: 183
Member Since:
February 1, 2019
sp_UserOfflineSmall Offline
11
May 27, 2021 - 8:45 pm
sp_Permalink sp_Print

Thank you very much for help!

I will do this.

Jacek

Avatar
jaryszek
Member
Members
Level 0
Forum Posts: 183
Member Since:
February 1, 2019
sp_UserOfflineSmall Offline
12
May 28, 2021 - 6:59 pm
sp_Permalink sp_Print

Ok Guys,

grab solution in attachment 😉 

Best,
Jacek

Avatar
jaryszek
Member
Members
Level 0
Forum Posts: 183
Member Since:
February 1, 2019
sp_UserOfflineSmall Offline
13
June 8, 2021 - 8:47 pm
sp_Permalink sp_Print sp_EditHistory

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1845
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
14
June 8, 2021 - 10:09 pm
sp_Permalink sp_Print sp_EditHistory

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:

1-1.jpgImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage 1-1.jpg (20 KB)
Avatar
jaryszek
Member
Members
Level 0
Forum Posts: 183
Member Since:
February 1, 2019
sp_UserOfflineSmall Offline
15
June 8, 2021 - 11:44 pm
sp_Permalink sp_Print

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1845
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
16
June 8, 2021 - 11:53 pm
sp_Permalink sp_Print

List.Select will analyze each item from the list.
"_" is the current item from the list.

Avatar
jaryszek
Member
Members
Level 0
Forum Posts: 183
Member Since:
February 1, 2019
sp_UserOfflineSmall Offline
17
June 9, 2021 - 12:08 am
sp_Permalink sp_Print sp_EditHistory

What do you mean by current?

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1845
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
18
June 9, 2021 - 12:37 am
sp_Permalink sp_Print sp_EditHistory

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

Avatar
jaryszek
Member
Members
Level 0
Forum Posts: 183
Member Since:
February 1, 2019
sp_UserOfflineSmall Offline
19
June 9, 2021 - 9:01 pm
sp_Permalink sp_Print

Thank you very much for such a nice explanation.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Philip Treacy, Peter Venkatrao, QSolutions Group
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 880
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 237
Jessica Stewart: 219
A.Maurizio: 213
Aye Mu: 201
Hans Hallebeek: 185
Newest Members:
Kate Dyka
Kwaje Alfred Mogga
thong nguyen
Appiagyei Kofi Frimpong
Hilary Burchfield
Richie Wright
Adel Kock
Barbara Murray
Doug Milne
Siobhan Stringer
Forum Stats:
Groups: 3
Forums: 24
Topics: 6546
Posts: 28655

 

Member Stats:
Guest Posters: 49
Members: 32832
Moderators: 2
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.