• 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
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Is there an INDEX/MATCH Equivalent in Power Query?|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Is there an INDEX/MATCH Equivalent in Power Query?|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 QueryIs there an INDEX/MATCH Equivalent …
sp_PrintTopic sp_TopicIcon
Is there an INDEX/MATCH Equivalent in Power Query?
Avatar
Stephen Griffin

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
June 15, 2021
sp_UserOfflineSmall Offline
1
June 15, 2021 - 8:27 pm
sp_Permalink sp_Print

While I'm knowledgeable with excel I am new to Power Query (been using it for about a week) and I'm struggling with some raw data from a .csv file. I'm struggling to find a work around within power query to automate a task. In excel I would use the INDEX/MATCH function to solve but I can feel that there is a better way in PQ.

 

My task is as follows:

  1. Download a .csv file from a third party containing raw data
  2. Use power query to tidy it up so as to have one row of data per set of information
  3. Upload the clean data onto my website

My problem:

  1. The raw .csv has each set of information duplicated over severals rows and I need one row per set of information
  2. The information is identical until it gets to the heading "Role" (see attachment) and then it forks and each "Role" has its own information (contact person, phone number etc.)
    1. NB: in the attached image I filtered the data to show one Information Set but the raw data has hundreds of different Information Sets

Capture.JPGImage Enlarger

 

My goal:

  1. Have a column/header for each and every Role type (which was easily done with Conditional Column)
  2. Have the follow on information (phone number of Role etc) fill up/down to make each row identical
  3. Remove duplicates to be left with one row per data set

 

Help:

  1. My knowledge of Power Query is failing me here. Fill Up/Down doesn't seem to be a solution because there is blank cells and fill down spills into the next information set
  2. I suppose I can Close and Load to excel and then manually Index/Match before removing duplicates, but then its the manual input I wanted to get away from doing.
  3. Can someone see a betetr way of doing this?

Many thanks in advance,

sp_PlupAttachments Attachments
  • sp_PlupImage Capture.JPG (80 KB)
Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
2
June 16, 2021 - 6:18 am
sp_Permalink sp_Print

Hello,

Can you share a sample file with some dummy data and also a mockup of what you want to achieve, that would make it easier to help you.

Br,
Anders

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4447
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
3
June 16, 2021 - 9:18 am
sp_Permalink sp_Print

Hi Stephen,

I think what you want is the wrong approach. The current layout of your data is correct. i.e. it's in a tabular format. Your goal of "Have a column/header for each and every Role type" would make this data very difficult to summarise/analyse later. Perhaps Power Query is the wrong tool for your goal. A PivotTable would be better, but it's difficult to say without seeing a sample Excel file containing your data (desensitised if required) and a mock up of your desired result.

Mynda

Avatar
Stephen Griffin

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
June 15, 2021
sp_UserOfflineSmall Offline
4
June 16, 2021 - 6:59 pm
sp_Permalink sp_Print

Thanks for taking the time to reply folks.

I should have clarified that the information isn't going to be displayed in excel; excel is only the vehicle to tidy up the data before exporting as .csv onto my website.

I'm restricted by the how the website wants the information input. Long story short, the wesbsite will display each excel row as a unique URL page, so I need to condense the information into one row.

See attachment with dummy data with the raw data on tab 1 and the desired format in tab 2.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
5
June 16, 2021 - 9:48 pm
sp_Permalink sp_Print

Hi Stephen,

Looks like a simple Pivot column operation:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Role]), "Role", "Company Name")
in
#"Pivoted Column"

However, you seem to have duplicate roles for the same project, is this possible in the real data? Not sure if it's just a sample data error.

For example, Project 4 has Roofing subcontractor role 3 times.

For this case, the solution is more complicated, duplicated should be merged under that role.

Avatar
Stephen Griffin

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
June 15, 2021
sp_UserOfflineSmall Offline
6
June 17, 2021 - 12:44 am
sp_Permalink sp_Print sp_EditHistory

Thank you Catalin,rn rnIt looks like you have done exactly what I was aiming to do (and yes, the raw data may contain a number of different companies fulfilling the same "roles" so that solution of merging would have been my next question!)

As I am new to PQ, I didn't quite follow each of the steps. I pasted each of your step into a word document so I could have it open while I followed it in PQ, but:

  1. I don't know where to post the Source code above. I thought maybe in View > Advanced Editor but it gave me and error: Expression.SyntaxError: Invalid identifier.
  2. I got as far as "Add custom column" and pasted in the code from your first step, albeit it showed up as a 'Function' whereas yours showed up as 'Table'.?
  3. I then looked at how to go about with the "Expanded custom" step but I see no option for this

How does one carry out the step "Expanded custom" - google was not very helpful. I even tried watching Pivot Column videos on YouTube.

I'm sorry, I really am quite puzzled and you kind of jumped to the final answer without showing your workings haha. Without taking up too much of your time, could you slow it down a bit and provide commentary on each step?

Sincerest thanks in advance and thansk for sticking with this one!

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
7
June 17, 2021 - 3:27 am
sp_Permalink sp_Print

Sorry, that merging question should be asked from the very beginning, because that changes the entire solution. It's not something you can patch on the go, it should be part of the design stage.

This time I took a guess based on the sample data, but it will be really helpful to have a clear image from the beginning, otherwise it will waste time.

The query is not easy to understand if you have no PQ experience unfortunately.

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Custom", (x)=> Table.SelectRows(Table.Group(Source, {"Project Title"}, {{"Count", each Table.SelectColumns(_,{"Role","Company Name"}), type table}}), each _[Project Title] =x[Project Title])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Count"}, {"Count"}),
#"Removed Duplicates" = Table.Distinct(#"Expanded Custom", {"Project Title"}),
#"Added Custom1" = Table.AddColumn(#"Removed Duplicates", "Custom", each ConvertTable([Count])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Count"}),
#"Expanded Custom1" = Table.ExpandTableColumn(#"Removed Columns", "Custom", Table.ColumnNames(Table.Combine(#"Removed Columns"[Custom])), Table.ColumnNames(Table.Combine(#"Removed Columns"[Custom])))
in
#"Expanded Custom1"

Added Custom: in this column, I grouped Roles and Companies for the current Project title.

After collecting the roles and companies, we can remove duplicate project rows, we have what we need in the added column;

in #"Added Custom1", we use the custom function ConvertTable, that performs the company merge by role and will also transpose the roles into columns, so it's an important step.

#"Expanded Custom1" will simply expand the table converted by ConvertTable function.

To help you understand how ConvertTable works, I added a sample table and a query that does the same thing as the function mentioned ("SampleRoleMerging" query) so you can see each step.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Velouria, Lionel Baijot, Jack Brett, Ivica Cvetkovski
Guest(s) 12
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Hayden Hao
Angela chen
Sean Moore
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Ray-Yu Yang
George Shihadeh
Naomi Rumble
Forum Stats:
Groups: 3
Forums: 24
Topics: 6211
Posts: 27239

 

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