I'm hoping one of you guys could help me with this.
Attached is some sample data I need transforming. When the file is opened our supplier provides the data in the format at the bottom of the sheet and the data needs to look like the top part to be able to get the data into our system.
Could the data be changed in the Power Query Editor?
I know there isn't much details here as this is hard one to explain but easy to see in the data provided.
Thank you in advance for your help.
Luke
Excel 2016 (Windows) 32 bit
SKU data 10000ES \\SEALEY-AX-DOCS\AX_ProductionDocArchive\PDFs\Instructions\10000ES_DFC29121.pdf ,\\SEALEY-AX-DOCS\AX_ProductionDocArchive\Images\WEBImage\10000ES_STRAIGHT_LOWERED_DFC0160852.png ,\\SEALEY-AX-DOCS\AX_ProductionDocArchive\PDFs\Parts\10000ES Parts Diagram 20190726 (3)_DFC0242267.pdf ,\\SEALEY-AX-DOCS\AX_ProductionDocArchive\Images\WEBMain\10000ES.png ,\\SEALEY-AX-DOCS\AX_ProductionDocArchive\PDFs\MSDS\10000ES_v1_SDS.pdf ,\\SEALEY-AX-DOCS\AX_ProductionDocArchive\Images\WEBImage\10000ES_CLOSE_DFC0590270.png ,\\SEALEY-AX-DOCS\AX_ProductionDocArchive\Images\WEBImage\10000ES_STRAIGHT_RAISED.png 1000E \\SEALEY-AX-DOCS\AX_ProductionDocArchive\Images\WEBMain\1000E.V2_DFC0105257.png ,\\SEALEY-AX-DOCS\AX_ProductionDocArchive\PDFs\Parts\1000E.V2 Parts Diagram 20190806 (3)_DFC0246658.pdf ,\\SEALEY-AX-DOCS\AX_ProductionDocArchive\PDFs\Instructions\1000E_V2.pdf ,\\SEALEY-AX-DOCS\AX_ProductionDocArchive\PDFs\MSDS\1000E_v2_SDS.pdf 1000ETJ \\SEALEY-AX-DOCS\AX_ProductionDocArchive\PDFs\Instructions\1000ETJ.pdf ,\\SEALEY-AX-DOCS\AX_ProductionDocArchive\Images\WEBMain\1000ETJ_DFC0105263.png ,\\SEALEY-AX-DOCS\AX_ProductionDocArchive\PDFs\Parts\1000ETJ Parts Diagram 20191009 (3)_DFC0279116.pdf ,\\SEALEY-AX-DOCS\AX_ProductionDocArchive\PDFs\MSDS\1000ETJ_v1_SDS.pdf ,\\SEALEY-AX-DOCS\AX_ProductionDocArchive\Images\WEBImage\1000ETJ_EXTENDED_DFC0153327.png ,\\SEALEY-AX-DOCS\AX_ProductionDocArchive\Images\WEBImage\1000ETJ_LOWERED_DFC0153328.png 1000TR \\SEALEY-AX-DOCS\AX_ProductionDocArchive\PDFs\MSDS\1000TR_v1_SDS.pdf ,\\SEALEY-AX-DOCS\AX_ProductionDocArchive\PDFs\Instructions\1000TR Instructions 20190801 (3)_DFC0244170.pdf ,\\SEALEY-AX-DOCS\AX_ProductionDocArchive\PDFs\Parts\1000TR Parts Diagram (3)_DFC0281983.pdf ,\\SEALEY-AX-DOCS\AX_ProductionDocArchive\Images\WEBMain\1000TR_DFC0105269.png ,\\SEALEY-AX-DOCS\AX_ProductionDocArchive\Images\WEBImage\1000TR_EXTENDED_DFC0153332.png
Sheet: Sheet2
Using Power Pivot and Concatenatex for a new measure presents as above. Is this acceptable?
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Hi Alansidman,
No our system wouldn't like that format.
It would need to be exactly like in the top of the sample data with the File Type as the column header and the value in the relevant cell.
I have 32,000 lines which need to be put in this format so it can be imported into our system.
It just needs to be the most simple way to get the data transformed as we have to do this twice a week.
Thanks
Here is the Power Query Pivot of the data
Excel 2016 (Windows) 32 bit
SKU Instructions Web Image Parts Diagrams Web Main Picture Material Safety Data Sheet 10000ES \\SEALEY-AX-DOCS\AX_ProductionDocArchive\PDFs\Instructions\10000ES_DFC29121.pdf \\SEALEY-AX-DOCS\AX_ProductionDocArchive\PDFs\Parts\10000ES Parts Diagram 20190726 (3)_DFC0242267.pdf \\SEALEY-AX-DOCS\AX_ProductionDocArchive\Images\WEBMain\10000ES.png \\SEALEY-AX-DOCS\AX_ProductionDocArchive\PDFs\MSDS\10000ES_v1_SDS.pdf 1000E \\SEALEY-AX-DOCS\AX_ProductionDocArchive\PDFs\Instructions\1000E_V2.pdf \\SEALEY-AX-DOCS\AX_ProductionDocArchive\PDFs\Parts\1000E.V2 Parts Diagram 20190806 (3)_DFC0246658.pdf \\SEALEY-AX-DOCS\AX_ProductionDocArchive\Images\WEBMain\1000E.V2_DFC0105257.png \\SEALEY-AX-DOCS\AX_ProductionDocArchive\PDFs\MSDS\1000E_v2_SDS.pdf 1000ETJ \\SEALEY-AX-DOCS\AX_ProductionDocArchive\PDFs\Instructions\1000ETJ.pdf \\SEALEY-AX-DOCS\AX_ProductionDocArchive\PDFs\Parts\1000ETJ Parts Diagram 20191009 (3)_DFC0279116.pdf \\SEALEY-AX-DOCS\AX_ProductionDocArchive\Images\WEBMain\1000ETJ_DFC0105263.png \\SEALEY-AX-DOCS\AX_ProductionDocArchive\PDFs\MSDS\1000ETJ_v1_SDS.pdf 1000TR \\SEALEY-AX-DOCS\AX_ProductionDocArchive\PDFs\Instructions\1000TR Instructions 20190801 (3)_DFC0244170.pdf \\SEALEY-AX-DOCS\AX_ProductionDocArchive\Images\WEBImage\1000TR_EXTENDED_DFC0153332.png \\SEALEY-AX-DOCS\AX_ProductionDocArchive\PDFs\Parts\1000TR Parts Diagram (3)_DFC0281983.pdf \\SEALEY-AX-DOCS\AX_ProductionDocArchive\Images\WEBMain\1000TR_DFC0105269.png \\SEALEY-AX-DOCS\AX_ProductionDocArchive\PDFs\MSDS\1000TR_v1_SDS.pdf
Sheet: Sheet2
and the related Mcode.
Please Login or Register to view this content.
How did you do this task as it looks nearly spot on?
Only last problem is the Web Image....Some of the SKUs have multiple Web image rows. Before the above is done, would it be possible to change the text in the File Type Column to Web Image 1, Web Image 2, Web Image 3 etc based on how many duplicate lines there is for that particular SKU?
IF you notice in the example you have sent the web image isn't in the data because there is 3 lines which has Web Image as the file type.
Thanks
Here is the file.
1. Bring data into PQ Editor
2. Pivot the data on the File Type
3. Select File Path as the data to Pivot
4. I did not change the column names but you can by clicking on the header and renaming.
5. Close and Load
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").
It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.
- Follow this link to learn how to install Power Query in Excel 2010 / 2013.
- Follow this link for an introduction to Power Query functionality.
- Follow this link for a video which demonstrates how to use Power Query code provided.
I have managed to get the results of what you have done already thank you.
However I need the Web Image names changing based on the number of times this is duplicated for that specific SKU. Is there a way to do this?
I have attached an example of how the data needs to look before the Power Query is actioned.
Thanks
Understand what you are looking for. I believe that you will need to do that prior to loading into PQ. Probably need some VBA to do that. I don't have a solution for you, but will ask others to assist on this.
That would be brilliant if someone else could help with this.
Thank you very much for your help it's much appreciated.
How's this using a helper query?
Rory
That looks brilliant Rory. How did you achieve this?
Thanks
Luke
I added an index column, then duplicated that query. In the duplicate I filtered for Web Image type, and then grouped by SKU returning the lowest index number for that SKU. Linking that into the original query on SKU number, we can then, for any Web Image column, subtract the lowest index number from the existing index number and add 1, so that we get a counter of 1, 2, 3 etc for each Web Image entry within an SKU number, then we append that to the type column, and pivot as Alan did originally. Make sense?
Please try
Please Login or Register to view this content.
Bo_Ry - I have just tried to add this to the following code but getting an error
Any ideas what would be causing the error when I copy your code in?PHP Code:let
Source = Excel.Workbook(File.Contents("****File Directory in here***"), null, true),
Export_Models_Images_videos_Sheet = Source{[Item="Export_Models_Images_videos",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Export_Models_Images_videos_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Model No", type text}, {"CONFIGID", Int64.Type}, {"File Name", type text}, {"Archive Path", type text}, {"File Type", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Model No] <> null and [Model No] <> ""),
#"Inserted Merged Column" = Table.AddColumn(#"Filtered Rows", "File Path", each Text.Combine({[Archive Path], [File Name]}), type text),
#"Reordered Columns" = Table.ReorderColumns(#"Inserted Merged Column",{"Model No", "CONFIGID", "File Path", "File Name", "Archive Path", "File Type"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"CONFIGID", "File Name", "Archive Path"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Model No", "SKU"}})
in
#"Renamed Columns"
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks