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:
?dl=0
Video: https://www.loom.com/share/dc7e690b4bdf4e8c85326ecefc6bb9b9
Files: https://www.dropbox.com/s/zz8hqdib5crbxzz/SC-In%20Person.zip?dl=0
Note: source files are under the folder SC-In Person_testing
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
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
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.
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...
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
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?
I understand it. thanks again.
