• 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

I don't see 3rd child's First Name & Last Name after "Expanded Table Column1"|Power Query|Excel Forum|My Online Training Hub

You are here: Home / I don't see 3rd child's First Name & Last Name after "Expanded Table Column1"|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 QueryI don't see 3rd child's First Name …
sp_PrintTopic sp_TopicIcon
I don't see 3rd child's First Name & Last Name after "Expanded Table Column1"
Avatar
Jim Chen
Member
Members

Power BI
Level 0
Forum Posts: 54
Member Since:
February 20, 2019
sp_UserOfflineSmall Offline
1
August 1, 2022 - 1:12 pm
sp_Permalink sp_Print

I have a total of 6 CSV files. Three of them contain a third child on column TP and column TQ:

File names
Column (first name)
Colum (Last name)
Chen family csv
TP
TQ
Gavrilov FamilyB.csv
TP
TQ
Chopra.csv
TP
TQ

However, after Power Query, I can only see a 3rd child from one file only: Chen family.csv The 3rd child from Gavriloc FamilyB.csv & Chopra.csv disappear.

You may see them under columns First Name_216 & Last Name_217 after I expanded the table in step 7: "Expanded Table Column1"

Can you please help me solve this issue? If I can see a 3rd child's FirstName & Last Name appear under different columns such as First Name_xyz & Last Name_abc, then I can combine First Name_216 & First Name_217 into one column, and combine Last name_217 & Last Name ABC into another column, but I don't see them appearing under different columns.

Image:

https://www.dropbox.com/s/ykq1.....1.PNG?dl=0

Video: https://www.loom.com/share/dc7.....cefc6bb9b9

Files: https://www.dropbox.com/s/zz8h.....n.zip?dl=0

Note: source files are under the folder SC-In Person_testing

Avatar
Jessica Stewart
Northern USA
Member
Members


Trusted Members
Level 0
Forum Posts: 202
Member Since:
February 13, 2021
sp_UserOfflineSmall Offline
2
August 2, 2022 - 1:43 am
sp_Permalink sp_Print

Hi Jim,

You can't see the third child because your names are different. In your source file, the column name is "First Name_216" and "Last Name_217" but for Gavrilov for example the name of the column is "First Name_186" and "Last Name_187" so PQ is looking for "First Name_216" and "Last Name_217", doesn't find that column so doesn't add it. If you go into your Sample File and remove the promote headers and promote the headers after you expand your file you will see the other family's 3rd child. You will need to filter out the headers from the other files after you promote, but it will solve your problem. 🙂

Jessica

Avatar
Jim Chen
Member
Members

Power BI
Level 0
Forum Posts: 54
Member Since:
February 20, 2019
sp_UserOfflineSmall Offline
3
August 2, 2022 - 10:18 pm
sp_Permalink sp_Print

Jessica,

Thank you very much for your time. First of all, there is a huge gap between my skills and yours so I appreciated your patience.

I know that there are 4 pairs of "First Name" & "Last name" for Parent, Frist Child, Second Child, and Third Child. That is the form setup. Since the column names are identical, so the Power Query auto-assigns a unique column name to each of the First Names & Last Names.

My questions:

1. Why does PQ assign different columns to the 3rd child: "First Name_186" and "Last Name_187" vs. "First Name_216" and "Last Name_217"?

2. "If you go into your Sample File and remove the promote headers": I don't see "promote headers" under Advanced Editor, and I don't know how to do it. can you help?

3. "promote the headers after you expand your file you will see the other family's 3rd child.": I don't know how. Can you help?

4. "You will need to filter out the headers from the other files after you promote": I don't know how. Can you help?

It is very challenging for me. Thanks for your time and patience.

Jim

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


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 441
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
4
August 2, 2022 - 11:56 pm
sp_Permalink sp_Print

You need to remove the "Promote Header" step from the Transform function (fx) as the CSV files do not have consistent column headers.

In stead, promote headers after expanding and filter out the remaining header rows.  I.e. all rows that have "Submission date" in the second column. Then you can do some cleaning up, but you don't need to add a hundred custom columns (2 for each week day over a 10 week period).

I couldn't re-instate the last few steps in your query, but the screenshot shows how the relevant columns for the 3rd children looks like for me.

Screenshot-2022-08-02-155134.pngImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage Screenshot-2022-08-02-155134.png (15 KB)
Avatar
Jessica Stewart
Northern USA
Member
Members


Trusted Members
Level 0
Forum Posts: 202
Member Since:
February 13, 2021
sp_UserOfflineSmall Offline
5
August 3, 2022 - 2:04 am
sp_Permalink sp_Print sp_EditHistory

Hi Jim, I'm happy to help! Let me answer your questions as best I can.

1. It gives them "_[number]" the same reason an excel table does, it can't have columns with the same names, so it gives it a unique one the different numbers are dependent on the number of columns it has used with that same name.

2. You don't need to use the advanced editor to achieve this. You can go into the function as Riny said, or go to your "Transform File" query and click the x next to promote headers on the "Applied Steps" pain, as shown in this screenshot. 

3. Then you go into your SC_InPerson file > go to "Expanded Table Column1" step > "Use First Row as Headers" on your Home Ribbon as seen here. It will as you if you want to insert a step, click to continue.

4. Next you will filter your rows, you do this the same as with an Excel Table. As seen here.

You may need to make a few tweaks to correct some trickled-down annoyances, but in the end, you will have Riny's screenshot above. If you go into the function itself as Riny suggested you will need to use the Advanced Editor, I opted not to try and explain that as you stated you were a newbie. I hope I made sense. I hope my screenshots came through, too. I guess we shall find out when I post...

Avatar
Jim Chen
Member
Members

Power BI
Level 0
Forum Posts: 54
Member Since:
February 20, 2019
sp_UserOfflineSmall Offline
6
August 6, 2022 - 11:16 am
sp_Permalink sp_Print

Riny & Jessica,

It works the way I want. Thank you very much for solving my problem, a very painful one.

Can you please tell me what is the difference about " promote headers before transform" and transfer after promote headers"?

Again, I appreciated all your help and time!

Jim

Avatar
Jessica Stewart
Northern USA
Member
Members


Trusted Members
Level 0
Forum Posts: 202
Member Since:
February 13, 2021
sp_UserOfflineSmall Offline
7
August 11, 2022 - 12:43 am
sp_Permalink sp_Print

PQ was promoting the headers in each file and then combining the files, which was creating your issue with multiple headers. By deleting the promote headers step in your example file and promoting the headers after combining the files you eliminate many files with the same name and only have that one file with the header that you need without the unique identifying number. Does that answer your question?

Avatar
Jim Chen
Member
Members

Power BI
Level 0
Forum Posts: 54
Member Since:
February 20, 2019
sp_UserOfflineSmall Offline
8
August 12, 2022 - 7:08 am
sp_Permalink sp_Print

I understand it. thanks again.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Velouria, Dario Serrati, Valentyn Kristioglo, Nada Perovic, Andrew Er
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Raj Mattoo
Mark Luke
terimeri dooriyan
Jack Aston
AndyC
Denise Lloyd
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Forum Stats:
Groups: 3
Forums: 24
Topics: 6219
Posts: 27279

 

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