Log In
Register
Home
December 6, 2020
Offline
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
December 6, 2020
Offline
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.
December 6, 2020
Offline
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.
December 6, 2020
Offline
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.
1 Guest(s)
