• 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

Power BI Query: Looping through Table.Selectrows function?|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Power BI Query: Looping through Table.Selectrows function?|Power Query|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
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 QueryPower BI Query: Looping through Tab…
sp_PrintTopic sp_TopicIcon
Power BI Query: Looping through Table.Selectrows function?
Page: 12Jump to page
Avatar
Trond Liavik
Member
Members
Level 0
Forum Posts: 44
Member Since:
June 22, 2019
sp_UserOfflineSmall Offline
1
May 20, 2022 - 2:27 am
sp_Permalink sp_Print sp_EditHistory

Hi folks!

I have two tables in Power BI, where on is the facts table with transactions (A), and the other being dimension table (B) with supplier contract info.

Table A consist of several columns, like TransactionDate, SupplierNr and more.

Table B consist of a few columns, like SupplierNr, DateFrom and DateTo. B is my contract table.

One column in table B is also showing the table-function, if possible to use Table.Selectedrows in order to loop through each rows while testing conditions.

Question:

How to add a new column in table A, witch tests and write «yes» or «no» for each matching SupplierNr, with regards to each TransactionDate in table A in between of DateFrom and DateTo in table B?

Not all SupplierNr are in table B. For speed its not necessary then to test every TransactionDate being in between DateFrom and DateTo, if SupplierNr is only in table A, and not always in table B.

There can also be more than one row for each SupplierNr in table B, as several contracts with same SupplierNr have different dates from and to.

In each new row in table A either «yes» or «no» should be written when refreshing table A. Where «yes» means contract exist to the SupplierNr and TransactionDate in contract period.

When no identic SupplierNr in table B, row in table A should just say «no», continuing the testing of SupplierNr (and TransactionDate) in next row of tableA.

In Power BI Report I can then filter on «yes» and «no» from table A after updating.

Only missing this in order to reach my goal with the Power BI report.

Thanx in advance, for anyone helping me out here!

Cheers, Trond

sp_AnswersTopicSeeAnswer See Answer
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOnlineSmall Online
2
May 23, 2022 - 2:28 pm
sp_Permalink sp_Print

Hi Trond,

You can merge those 2 tables using as key the Supplier Nr.

In the new merge column, you will have tables, with 0 or more rows, depending on how many contracts are in table B for that SupplierNr.

Add a new column named "Has Contracts" with a simple formula to check if the number of rows in that merge column is greater than 0:

=if Table.RowCount([Merged])>0 then "Yes" else "No"

Avatar
Trond Liavik
Member
Members
Level 0
Forum Posts: 44
Member Since:
June 22, 2019
sp_UserOfflineSmall Offline
3
May 23, 2022 - 9:00 pm
sp_Permalink sp_Print

Hi Catalin,

Sorry to say, but your suggestion neither solves the question regarding matching SupplNr, nor the problem with nested loop for TransactionDate being between DateFrom and DateTo.

Trond

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 490
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
4
May 24, 2022 - 2:24 pm
sp_Permalink sp_Print

Perhaps the attached (updated) file contains the solution you asked for.

Avatar
Trond Liavik
Member
Members
Level 0
Forum Posts: 44
Member Since:
June 22, 2019
sp_UserOfflineSmall Offline
5
May 26, 2022 - 8:29 am
sp_Permalink sp_Print sp_EditHistory

Hi Riny van Eekelen,

You have obviously done something workable in the attached file.. I'll try following applaid steps..  But how does the function "earliest transaction" work?Smile

I have an extended question too: What if a column of Product categories with numbers from 1000-9999 are added to the transaction table A at bookkeeping (just one number each line of course). One subgroup can be 1005 'car repairs', another 2003 'travel', a third 3404 'bying cars' etc. The earlier total in transaction table A are then split into one or more lines when bookkeeping. In contract table B these subgroups should be listet at each contract line in a common cell, example like: '2003, 3004, 5007' for a specific contract with from and to date. In this example then Yes should be written only for the subgroup 2003 travel, meaning suppliers & transaction date & product category being true at same time - else No should be written. If possible this actually can make me smile! Laugh

- last question above is just looking a bit into the future in Power BI..witch could be the ultimate analyse report for uncovering breach of contracts with suppliers, witch often are contracts made not just with dates from and to pr supplier, but also pr Product category (in my country at least).

Trond

Avatar
Trond Liavik
Member
Members
Level 0
Forum Posts: 44
Member Since:
June 22, 2019
sp_UserOfflineSmall Offline
6
May 26, 2022 - 10:27 am
sp_Permalink sp_Print sp_EditHistory

Riny:

Btw, getting some error when copying the sentence in Merged table (Source step). The table that are produced in a column, has null in all its columns, when clicking on it. Probably meaning the two tables (A and B in comparison) doesn't connect in my production model.

I'm in Power Query now. Perhaps the failure is if not possible to merge a merged table with the transaction table? (as I’m not able to press the button option to merge two tables).

Trond

sp_PlupAttachments Attachments
  • sp_PlupImage error-implementing-earliest_transaction.png (17 KB)
Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 490
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
7
May 26, 2022 - 4:01 pm
sp_Permalink sp_Print

Hi Trond,

The "earliest_transaction" serves as a variable used to filter out contracts from Table B (before merging) that have an expiry date before the earliest transaction date in Table A. So, it's not a function!

You can see in the Applied Steps how I find the earliest date with List.Min( ). Or actually, just by clicking on the TransactionDate column and then on the Transfer tab, Date, Earliest.

With regard to your extended question, it's difficult to visualise exactly what you need. Perhaps you can upload a file with a representative example of the data you are working with or envisage to be working with in the future.

With regard to the error message, make sure that the name of the previous step is spelled correctly. And if the error persists, you can upload a file producing the error. Much easier to help that way.

Best,

R

Avatar
Trond Liavik
Member
Members
Level 0
Forum Posts: 44
Member Since:
June 22, 2019
sp_UserOfflineSmall Offline
8
May 27, 2022 - 3:23 am
sp_Permalink sp_Print

Hi Riny,

Okey, lets focus in two steps: 1) getting the existing file to work as intended, and 2) consider an extension regarding Product categories (not pri right now).

Attached is the file you made for me, with one minor change in table A (orange marked), in order to move the earliest_transaction filter "aside" when testing.

As the screendump shows for SupplierNr 227, this can't be both Yes and No at same time. The result output should just show SupplierNr 227 one time for the amount 450. In this case the Yes line is correct, as a contract exist in the actual period for SupplierNr 227, as more than one contract periode exist in table B.

Trond

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 490
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
9
May 27, 2022 - 3:48 am
sp_Permalink sp_Print

You didn't attach a file. Please try again and don't forget to press the Upload button once you added a file.

Avatar
Trond Liavik
Member
Members
Level 0
Forum Posts: 44
Member Since:
June 22, 2019
sp_UserOfflineSmall Offline
10
May 27, 2022 - 4:43 am
sp_Permalink sp_Print

Hi Riny,

Files attached (thought I did that last post butreturning-same-SupplierNr-several-times-with-same-amount-and-both-Yes-and-No-1.PNGImage Enlarger

I probably forgot uploading first)

Trond

sp_PlupAttachments Attachments
  • sp_PlupImage returning-same-SupplierNr-several-times-with-same-amount-and-both-Yes-and-No-1.PNG (210 KB)
Avatar
Trond Liavik
Member
Members
Level 0
Forum Posts: 44
Member Since:
June 22, 2019
sp_UserOfflineSmall Offline
11
May 27, 2022 - 8:55 am
sp_Permalink sp_Print sp_EditHistory

Hi Riny and others,

I also have another suggestion/or question to solution, using iterations (looping). See Yes-or-No-code-testing-OrganizationNr-missing-test-of-TransactionDate-in-between-of-DateFrom-and-DateTo-1.PNGImage Enlarger
new & better Power BI AGREEMENTS-List-DateFrom.PNGImage Enlarger
files attached!

Each row in TRANSACTIONS[OrganisationNr] isAGREEMENTS-List-DateTo-1.PNGImage Enlarger
AGREEMENTS-List-DateFrom-1.PNGImage Enlarger
first tested if match to AGREEMENTS[OrganisationNr]. My file allready does this now, see code in last step of TRANSACTION table in PQ (Power Query).

Btw, I made the PQ file by importing "Bying" and "Rentals" tables first, then making references (ref) of those tables, before merging these last two into a common AGREEMENTS table. The AGREEMENTS table is what should be tested against the TRANSACTIONS table for OrganizationNr and TransactionDate.

See the 4 steps in table "AGREEMENTS" in PQ, and specially the columms with formula making List of DateFrom and DateTo. Some of these OrganizationNr actually have more than 1 rows in Lists, making List in theory ideal for looping through each OrganizationNr, as OrganizationNr now is grouped avoiding any duplicates in AGREEMENTS table

Also see very last step in "TRANSACTION" table, some code I found on internet and tweeked bit, for doing a lookup test each OrganizationNr in TRANSACTIONS table if existing iAGREEMENTS-List-DateTo.PNGImage Enlarger
n AGREEMENTS table too.
My question is then finally:

Can someone help making the code for iterations in the very last step of TRANSACTION table, testing each List row in AGREEMENTS table of DateFrom and DateTo columns, by checking every TransactionDate if in between of those List AGREEMENT dates?

I have a good feeling function List.Generate() could do this looping for me!?? But how to code this.. :/

The logic of course is, if same OrganizationNr is in both tables _and_ TRANSACTIONS[TransactionDate] between some of the dates in any rows in Lists in AGREEMENTS table, then Yes should be written as filter to the last column of TRANSACTIONS table. Today this column only test for OrganizationNr as I've explained, and no TransactionDate testing unfortunately yet.

Gotta sleep some too - so long

TrondAGREEMENTS-List-DateFrom.PNGImage Enlarger

AGREEMENTS-List-DateTo.PNGImage Enlarger
Yes-or-No-code-testing-OrganizationNr-missing-test-of-TransactionDate-in-between-of-DateFrom-and-DateTo-2.PNGImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage AGREEMENTS-List-DateFrom.PNG (314 KB)
  • sp_PlupImage AGREEMENTS-List-DateTo.PNG (285 KB)
  • sp_PlupImage Yes-or-No-code-testing-OrganizationNr-missing-test-of-TransactionDate-in-between-of-DateFrom-and-Dat (344 KB)
Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 490
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
12
May 27, 2022 - 1:39 pm
sp_Permalink sp_Print

Can't really follow your intentions, based on text and a few screenshots. The xlsx you attached was the mock-up you provided earlier, but you changed the rules a bit by introducing an eleven year old transaction. How realistic is that? Despite the fact that supplier 330 has no contract that matches that particular date you want to see "yes" anyway. Obviously, the query returns "no".

And with regard to supplier 227, what would you want the outcome to be if we were to introduce a transaction dated 13 May 2019? Would such a transaction fall under both contracts, even though they might be different contracts all together? Or would you just accept a single "yes" because there is a contract?

And where does the column "VoucherNr" in the Merge query come from?

Obviously, my fairly simple solution is not capable of dealing with these complexity. Perhaps better that you upload a more realistic model of your data and include the queries that you have already created. That would make it so much easier:

sp_AnswersTopicAnswer
Answers Post
Avatar
Trond Liavik
Member
Members
Level 0
Forum Posts: 44
Member Since:
June 22, 2019
sp_UserOfflineSmall Offline
13
May 27, 2022 - 4:59 pm
sp_Permalink sp_Print sp_EditHistory

Hi Riny,

Example dates can ba some old, in production at least those will be mostly 12 months old. Sorry for a bit random transactions dates if so. Agreements dates on the other hand can be like 10 years old... However, in my opinion this should not change the technical issues I’m facing.

Regarding your previous post in merge mode, the SupplNr 227 came twice, as both Yes and No at the amount of 450. See my post 27th May about this. Witch led be believe this case is not possible to solve by using just merge statements.

The .pbix file I uploaded last night is really all that I wanna solve. But need the looping script for Trancaction dates in between of aggrement dates.

Of course a transaction table in accounting consist of voucher numbers too, but this is still a minor difference in all. My transaction table in real possibly consist of 20+ columns (amount, TransactionDate and voucher number only some of those). But it’s no testing for voucher number.

DateFrom and DateTo are absolute dates, meaning if a transaction happens 13th May, and the agreements table goes from 13th May to end of May same year, then Yes should be written in column.

Thanks for at least trying to help.

Trond

 
Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 490
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
14
May 27, 2022 - 5:32 pm
sp_Permalink sp_Print

Fair enough. Then perhaps another rather simplistic approach will work, as demonstrated in the attached file. No need for "earliest_date". 

Avatar
Trond Liavik
Member
Members
Level 0
Forum Posts: 44
Member Since:
June 22, 2019
sp_UserOfflineSmall Offline
15
May 28, 2022 - 3:50 am
sp_Permalink sp_Print

Hi Riny,

Opened your last file. To last step Merged table: For SupplNr 227, is it random that No disappear, leaving Yes behind? (seems like a 50% chance to me, or is this functionality part of the Disctinct formula, always choosing Yes before No...?!)

Btw, liked your "earliest_date" formula. Will use it in future reports too.

Trond

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 490
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
16
May 28, 2022 - 6:54 pm
sp_Permalink sp_Print

The "No" doesn't just disappear randomly. Sorting by supplier (ascending) and Yes/No (descending) will put a "Yes" row above a "No" row for those transactions where there are multiple hits. Removing duplicates where date AND supplier AND amount are the same, automatically keeps the "Yes" row as it will be the first one.

Avatar
Trond Liavik
Member
Members
Level 0
Forum Posts: 44
Member Since:
June 22, 2019
sp_UserOfflineSmall Offline
17
May 29, 2022 - 1:40 am
sp_Permalink sp_Print sp_EditHistory

Riny:

Aha! I didn't know AND is prioritized before OR when using DistinctLaugh (wonder what happens if more than one Yes same SupplNr on different dates, all within agreement dates)

Is the SupplierNr match test done in first step of merged table?

(btw, do you know what to do when the button for merging is grey and not available no longer..is it still possible to write the merge code as script in advanced editor, and how do I insert of a new table in PQ to do that - seems I'm only able to insert a blank query..)

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 490
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
18
May 29, 2022 - 2:34 pm
sp_Permalink sp_Print

Well, if the dates are not the same, you'll end up with two "yes" rows.

The first step in the Merge query is indeed matching up the Suppliers as you can see from the first step in the AE. Note that I chose to do "Merge as new" so that a new query gets created in the first step like this:

Source = Table.NestedJoin(TableB, {"SupplierNr"}, TableA, {"SupplierNr"}, "TableA", JoinKind.FullOuter)
 
Regarding the Merge-button, I perhaps don't understand the question? Are you referring to the Merge Queries button? For me that's gray when the query is totally blank. I haven't come across any other situation or just didn't notice it. When does it happen for you?
 
If you are talking about Merge columns, you most select at least two columns for the Merge button to become active.
 
Avatar
Trond Liavik
Member
Members
Level 0
Forum Posts: 44
Member Since:
June 22, 2019
sp_UserOfflineSmall Offline
19
May 29, 2022 - 6:19 pm
sp_Permalink sp_Print sp_EditHistory

Hi Riny,

Could you try Merge button in Power BI Query yourself, see attachment. I'm not able to choose Merge button there, it's all greyed out. Really stopping on this point..not getting further. Also I've changed the datatype for SupplNr to text, as its' imported in numbers initial.
-> What is possible with regards to merging in Excel Query view, seems _not_ possible in Power BI Query under Transform menu.

Could I alternatively write this code in a blank query (or an empty table, but can't find a choice for empty table in PQ, any tips to what allready tried..?)
Source = Table.NestedJoin(TableB, {"SupplierNr"}, TableA, {"SupplierNr"}, "TableA", JoinKind.FullOuter)

Trondunable-to-choose-the-Merge-button-in-PBI-Query.pngImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage unable-to-choose-the-Merge-button-in-PBI-Query.png (216 KB)
Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 490
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
20
May 29, 2022 - 7:13 pm
sp_Permalink sp_Print sp_EditHistory

Your picture points to the Merge Columns button. As said, you need to select at least two columns for the button to become active.

Adding a blank query is under Get Data, From Other Sources. And yes, when you press the "Blank Query" button you can write the code directly in the formula bar, but leave out "Source". Note that this merges TableB and TableA, by SupplierNr. and that it has nothing to do with Merge Columns.

The easiest way would be to select the TableB query and press Merge Queries on the Home tab and then Merge Queries as New. Don't know the Norwegian on that, sorry. Added some screenshots to clarify.

Screenshot-2022-05-29-110409.pngImage Enlarger

Screenshot-2022-05-29-111155.pngImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage Screenshot-2022-05-29-110409.png (106 KB)
  • sp_PlupImage Screenshot-2022-05-29-111155.png (25 KB)
Page: 12Jump to page
sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Catalin Bombea, Vishal Nevase, John Kobiela, Malcolm Sawyer
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 216
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Melanie Ford
Isaac Felbah
Adele Glover
Hitesh Asrani
Rohan Abraham
Anthony van Riessen
Erlinda Eloriaga
Abisola Ogundele
MARTYN STERRY
Rahim Lakhani
Forum Stats:
Groups: 3
Forums: 24
Topics: 6356
Posts: 27793

 

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

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • 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
 

Company

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

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.