Log In
Register
Home
Active Member
July 22, 2022
Offline
I have one sheet with a long list of unique account numbers (account key). I have another sheet where each account key has multiple rows with data set out in different columns (the F, H and S Terms).
I am looking to do a lookup to consolidate the data so each Key has just one line and I can list the F, H and S on the same line. Formulas for the yellow cells on the Keys tab basically.
I am looking to do this in Power Query ideally but would be good to know how to do in Excel also.
Many thanks
Trusted Members
Moderators
January 31, 2022
Offline
Hi David,
Looked at your file but it's not clear what you want to achieve. The file includes two queries, presumably to each of the data ranges in the two sheets. But how would the end result have to look like?
R
VIP
Trusted Members
December 7, 2016
Offline
Hello,
If you rearrange your data as shown in Data (2) sheet you can easily get what you want with a Pivot Table.
I have not created such Pivot Table for you, but do give a shout out if you want help with creating one.
Br,
Anders
VIP
Trusted Members
December 7, 2016
Offline
Hello again,
In attached file you now have such Pivot Table and I have used Power Query to rearrange the data structure to correct one.
Br,
Anders
Active Member
July 22, 2022
Offline
Hi Anders,
Thanks for your response, that doesn't work the values for the terms are in text format i.e. 30 Days End of Month, 52 End of Month etc if I unpivot and try and summarize in a pivot table the values are useless. That example was just to try and illustrate my problem, my actual spreadsheet is rather large and has a load of other columns in as well as the terms.
If I put the account key into a single list with no duplicates and try to merge it with the master table I want to look up into whenever it see's a duplicate value it adds another row when this is not what I want. I want a single list (no duplicates) of all the account keys and to basically returns the first non blank value for the designated columns i.e. the F,H,S terms (there is more that I will need but just put those so someone could hopefully show me how this is done)
Thanks
Dave
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Ok, even if the values are text you can use Power Pivot with the help of a measure to show the text data as values. See attached file for such example.
Br,
Anders
Active Member
July 22, 2022
Offline
If I do that it duplicates the terms where there is more than one term for that type, I wish to basically do a vlookup and return the first non blank value for a designated column. Can this not be done in Power Query, I have the below formula which returns the first match (whether this is blank or not), can this not be adjusted somehow to return the first non blank match?
= try SupplierAccountsImport{Table.PositionOf(SupplierAccountsImport[[Account Key]],[Account Key=[UniqueKey]])}[Haulier Terms] otherwise null
Thanks
Dave
1 Guest(s)
