Apr 13 2021 01:22 PM
Apr 13 2021 01:22 PM
I am trying to convert product data so I can upload it into a website in its specific format. To do so I need to change the layout of the current file so I can move the data. So I need to put the data from D into the specific columns that I made from C and it must match the part numbers. There is 298 Unique attributes from column C that I made into each of their own columns. I need to match the correlating part numbers and take the data from D to its correct part number location and column. I am lost completely on this.
So IF E2=A2 and AU1=C2, return D2, then must count again and IF E2=A3 and BS1=C3 return D3. Any help would be awesome.
Apr 14 2021 01:10 AM - edited Apr 14 2021 01:11 AMSolution
@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.
Apr 14 2021 05:56 AM
Apr 14 2021 06:31 AM
@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:
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.
Apr 25 2021 08:28 PM
@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.
Apr 25 2021 09:32 PM
Can offer you two solutions. Choose the one you like best.
Power Query: Connect to the blue table, pivot the Question column with Answer as the values, without aggregation. Load to Excel.
Power Pivot: Add the blue table to the Data Model and create a DAX measure for the Answer column like:
=CONCATENATEX( Table1, Table1[Answer] )
Create a pivot valve with Questions in the column field, Name in the row field and the newly created measure in the value field.
Both solutions are included in the attached file.
Apr 25 2021 10:16 PM