Forum Discussion
Help moving data and matching to correct columns and rows.
turboedbird Are you familiar with Power Query (PQ)?
It would take only a few steps (connect to the csv file, keep three columns, promote headers, do some sorting, data typing and cleaning-up and then pivot the attribute column). Now, if you are not familiar with PQ, this will mean absolutely nothing to you.
I've done all that but noticed that, for instance, part number 300104ERL has one attribute called "Compatible Hose" with 5 different values on 5 different rows. The pivot step mentioned above couldn't handle this, so an extra step had done to group such items. But then the five different hose types are merged into one cell. Not sure if that is a problem for you or not.
The end result is a table with 4203 rows and 298 columns. File attached, though you will not be able to connect to the source file as it was linked to a location on my computer. Now, if you do know PQ, you'll be able to fix that yourself.
turboedbird Are you familiar with Power Query (PQ)?
It would take only a few steps (connect to the csv file, keep three columns, promote headers, do some sorting, data typing and cleaning-up and then pivot the attribute column). Now, if you are not familiar with PQ, this will mean absolutely nothing to you.
I've done all that but noticed that, for instance, part number 300104ERL has one attribute called "Compatible Hose" with 5 different values on 5 different rows. The pivot step mentioned above couldn't handle this, so an extra step had done to group such items. But then the five different hose types are merged into one cell. Not sure if that is a problem for you or not.
The end result is a table with 4203 rows and 298 columns. File attached, though you will not be able to connect to the source file as it was linked to a location on my computer. Now, if you do know PQ, you'll be able to fix that yourself.
- Thank you for the work and information it is greatly appreciated. I spent a lot of hours trying different indexes, VLOOKUPS, MATCHES and just getting stuck in the same thing.
I have never heard of PQ, but I will be downloading it now and trying to figure it out. Since this was only for one brand I will still need to learn to do this for all of the other brands that will be imported. So looking at the steps you took, I will need to:
1. connect to the csv file
2. keep three columns (Partnumber, Attribute, Value) I am assuming these three.
3. promote headers (Promote Attributes)?
4. do some sorting (What did you sort on?)
5. data typing and cleaning-up (Not sure if this is just making it look pretty or something else?)
6. then pivot the attribute column (When you pivot the column does the data follow at that point?)
Extra step for grouping multiple items with the same attribute (Does PQ allow you to group without losing data?)
I think I can figure most of those out with some googling but may need some clarification on the data typing and cleaning-up? Would it be possible for you to elaborate on these for me? Again thank you for all the help it is greatly appreciated.turboedbird Difficult to explain all of it in text here, but there are many resources on-line where you can learn the basics. For instance here:
https://exceloffthegrid.com/power-query-introduction/
If you are on a Windows version of Excel (2016 or thereafter), PQ is included. In more recent version you'll find the tools on the Data ribbon under "Get and transform data".
Once you have mastered the basics, you'll be able to follow the steps applied. If nor, com back here.
Riny_van_Eekelen Hi, I think I have a similar issue, but on a smaller scale. The answer may lie in Power BI, which someone else is looking into, but for now, I am getting csv files from data from our client database, and even if it's cleaned up in Power Bi, when we export out, it appears like table on left in pic below.
We want it to end up like Table on right in pic. I've done a little playing with PQ, but can't get it to get data to stay married properly, without just ending up with hundreds of columns and the questions repeated etc
(This isn't the actual data, but the set up is identical.) Have attached this spreadsheet.
Any help very much appreciated.
