• 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

Self Referencing Queries|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Self Referencing Queries|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 QuerySelf Referencing Queries
sp_PrintTopic sp_TopicIcon
Self Referencing Queries
Avatar
Dave White
Member
Members
Level 0
Forum Posts: 10
Member Since:
December 6, 2020
sp_UserOfflineSmall Offline
1
January 17, 2021 - 7:07 pm
sp_Permalink sp_Print

I am trying to tag CSV data feeds in Excel using Power Query

Here is what I am doing ..
Point Power Query to the Sharepoint Folder with the CSV files
Create a connection-only Transform Query with a calculated column to assign UniqueIDs
Load the Transform Query to an Excel Table
Add columns for the tags
Load the Expanded Table as a connection-only query in Power Query
Create an connection-only Outer Left Join on the UniqueID between the Transform Query and the Expanded Table Query

This is as I have read the instructions developed by Matt Allington, Ken Puls and others.

Here is what is going wrong ..
When I refresh the Data Source the Tag entries in Expanded Table columns do not stick with the correct UniqueID
It is not immediately obvious how the wrong row for the tag values is being allocated and it appears quite random

If anyone understands what is going on here and can help me resolve it I would be most grateful.

Dave White, South West England

  

Avatar
Dave White
Member
Members
Level 0
Forum Posts: 10
Member Since:
December 6, 2020
sp_UserOfflineSmall Offline
2
January 17, 2021 - 9:52 pm
sp_Permalink sp_Print

Further to my original post, I have worked out the sort order that is being applied.
In the Query uploaded from the Expanded Table the tags remain associated with the correct UniqueIDs after the refresh.
But in the Expanded Table itself and in the Merged Query they are associated with the Unique ID that now bears the same position in the sort order of the Expanded Table that was previously occupied by the tagged UniqueID.

This is so close to a solution that will save myself and my clients a lot of time and effort.

But I suspect I am bending the Power Query rules just a little too much!

Any thoughts gratefully received.
 

Avatar
Dave White
Member
Members
Level 0
Forum Posts: 10
Member Since:
December 6, 2020
sp_UserOfflineSmall Offline
3
January 18, 2021 - 3:29 am
sp_Permalink sp_Print

I think I may have uncovered my mistake ..

In Step Six I was creating an additional, separate, merge query - see my original text below -
Create an connection-only Outer Left Join on the UniqueID between the Transform Query and the Expanded Table Query

What I should have done was to edit the Main Query to include the Join
This allows the feed to be tagged "on the way in" as it were

The tagging is now a lot more stable, though it still can throw some glitches.

I can now create multiple columns for the tagging - although a word of caution this does need to be done at the start
Adding columns after the initial tagging will throw the self-reference query out 

I am now working on including formulas in the tagging sheet so it can be used for reports.

Avatar
Dave White
Member
Members
Level 0
Forum Posts: 10
Member Since:
December 6, 2020
sp_UserOfflineSmall Offline
4
January 18, 2021 - 10:16 pm
sp_Permalink sp_Print

A final word on this in case anyone is following the thread.

My goal was to tag external data feeds with locally controlled values.
I am using Power Query to automate the interaction between Bank Statement files, Receipt Bank Archives and Cloud Accounts packages.
And Power Query has dramatically improved the functionality on offer. 

The examples online refer to creating copies of tables to achieve the self-reference, but this is a little misleading.
I eventually unlocked the problem by understanding the flow of the information.

In the end result, a Transform Query grabs the CSV files from the Bank, adds a UniqueID from the data and outputs to a Table
Additional columns are then manually added to the table to hold things like Organisation Name and Category
The expanded table is loaded into Power Query where all columns are removed save the UniqueID and the manually added columns
An outer join between the two queries on the UniqueID is created as a new step in the Transform Query - no separate merge query is created

Now the data is loaded from the Sharepoint Folder, combined, transformed and tagged with any pre-existing local values whenever the table is refreshed
On the refresh any new local values are uploaded to Power Query to be available for the next refresh of the source data

Fingers crossed all is working fine and will save countless hours of work.

One final word of warning!

My first attempt at a UniqueID used a composite key from three fields within the data
And it turned out this was not a unique key
As a result some duplicate values crept in to the output table, and on each refresh the number of records expanded.
Very quickly my output table was holding double the number of records that existed in the source CSV files.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4519
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
5
January 20, 2021 - 7:01 pm
sp_Permalink sp_Print

Thanks for sharing, Dave!

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Velouria, jonhn wikal, Alain Balken, David du Toit
Guest(s) 10
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: 218
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Karen Newman
Aminu Sule
Samuel Ramirez
Abdifatah Osman
mohad borhom
Abayomi Adedeji
Ganesh MVS
Gilbert Lemek
Ashleigh Farquharson
Jayz Luu
Forum Stats:
Groups: 3
Forums: 24
Topics: 6364
Posts: 27830

 

Member Stats:
Guest Posters: 49
Members: 32347
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.