formula help

[factoryf]

Hi Guys
i have some data i get with two basic columns part number and image link, i need to import these into my site with the first column being the part number and the image link, then the next image link

here is one image link ( i did this )
[TABLE="width: 823"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]sku[/TD]
[TD]pic[/TD]
[/TR]
[TR]
[TD]BM1TCBK[/TD]
[TD]https://www.catalograck.com/ImgVDHR/NFA/BM1TCBK.jpg[/TD]
[/TR]
[TR]
[TD]BM1TCBK-TX[/TD]
[TD]https://www.catalograck.com/ImgVDHR/NFA/BM1TCBK.jpg[/TD]
[/TR]
[TR]
[TD]BM1TCBL[/TD]
[TD]https://www.catalograck.com/ImgVDHR/NFA/BM1TCBL.jpg[/TD]
[/TR]
[TR]
[TD]BM1TCBL-TX[/TD]
[TD]https://www.catalograck.com/ImgVDHR/NFA/BM1TCBL.jpg[/TD]
[/TR]
[TR]
[TD]BM1TCRD[/TD]
[TD]https://www.catalograck.com/ImgVDHR/NFA/BM1TCR.jpg[/TD]
[/TR]
[TR]
[TD]BM1TCRD-TX[/TD]
[TD]https://www.catalograck.com/ImgVDHR/NFA/BM1TCR.jpg[/TD]
[/TR]
[TR]
[TD]BM1TSBK[/TD]
[TD]https://www.catalograck.com/ImgVDHR/NFA/BM1TSBK-1.jpg[/TD]
[/TR]
[TR]
[TD]BM1TSBL[/TD]
[TD]https://www.catalograck.com/ImgVDHR/NFA/BM1TSBL-1.jpg
[/TD]
[/TR]
</tbody>[/TABLE]

here is how i get the data

[TABLE="width: 949"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]D023LH[/TD]
[TD]https://www.catalograck.com/ImgVDHR/NFA/D023LH-1.jpg[/TD]
[/TR]
[TR]
[TD]D023LH[/TD]
[TD]https://www.catalograck.com/ImgVDHR/NFA/D023LH-2.jpg[/TD]
[/TR]
[TR]
[TD]D023LH[/TD]
[TD]https://www.catalograck.com/ImgVDHR/NFA/D023LH-4.jpg[/TD]
[/TR]
[TR]
[TD]D023LH[/TD]
[TD]https://www.catalograck.com/ImgVDHR/NFA/D023LH-5.jpg[/TD]
[/TR]
</tbody>[/TABLE]


here is how i need the data

[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]part number[/TD]
[TD="width: 64"]pic1[/TD]
[TD="width: 64"]pic2[/TD]
[TD="width: 64"]pic3[/TD]
[TD="width: 64"]pic4[/TD]
[/TR]
[TR]
[TD]D023LH[/TD]
[TD]https://www.catalograck.com/ImgVDHR/NFA/D023LH-1.jpg[/TD]
[TD]https://www.catalograck.com/ImgVDHR/NFA/D023LH-2.jpg[/TD]
[TD]https://www.catalograck.com/ImgVDHR/NFA/D023LH-4.jpg[/TD]
[TD]https://www.catalograck.com/ImgVDHR/NFA/D023LH-5.jpg[/TD]
[/TR]
</tbody>[/TABLE]


thanks in advance

 

[sandy666]

with PowerQuery (Get&Transform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]SKU[/td][td=bgcolor:#5B9BD5]Pic[/td][td][/td][td=bgcolor:#70AD47]SKU[/td][td=bgcolor:#70AD47]Pic.1[/td][td=bgcolor:#70AD47]Pic.2[/td][td=bgcolor:#70AD47]Pic.3[/td][td=bgcolor:#70AD47]Pic.4[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]D023LH[/td][td=bgcolor:#DDEBF7]https://www.catalograck.com/ImgVDHR/NFA/D023LH-1.jpg[/td][td][/td][td=bgcolor:#E2EFDA]D023LH[/td][td=bgcolor:#E2EFDA]https://www.catalograck.com/ImgVDHR/NFA/D023LH-1.jpg[/td][td=bgcolor:#E2EFDA]https://www.catalograck.com/ImgVDHR/NFA/D023LH-2.jpg[/td][td=bgcolor:#E2EFDA]https://www.catalograck.com/ImgVDHR/NFA/D023LH-4.jpg[/td][td=bgcolor:#E2EFDA]https://www.catalograck.com/ImgVDHR/NFA/D023LH-5.jpg[/td][/tr]

[tr=bgcolor:#FFFFFF][td]D023LH[/td][td]https://www.catalograck.com/ImgVDHR/NFA/D023LH-2.jpg[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]D023LH[/td][td=bgcolor:#DDEBF7]https://www.catalograck.com/ImgVDHR/NFA/D023LH-4.jpg[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]D023LH[/td][td]https://www.catalograck.com/ImgVDHR/NFA/D023LH-5.jpg[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"SKU"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Pic", each Table.Column([Count],"Pic")),
    Extract = Table.TransformColumns(List, {"Pic", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Split = Table.SplitColumn(Extract, "Pic", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Pic.1", "Pic.2", "Pic.3", "Pic.4"})
in
    Split[/SIZE]

 

[factoryf]

Thanks Sandy666
forgive my second question is that a macro?

 

[sandy666]

Read first line in post#2

you'll need Excel 2010/2013 with PowerQuery add-in or higher version with PowerQuery (Get&Transform) build-in

What is PowerQuery

and no, this is not a macro this M-code for PowerQuery (Get&Transform)

 

[factoryf]

ok got all that and i have the latest version so that's all set, and i have been using this just to this extent. it kicks me an error "can't find table1" also i only have two columns one is sku and one is pic there could 1 pic per sku or 10 will this still work?

 

[sandy666]

in post#2 blue table is a Excel Table (Ctrl+T)
check the name of your new table and use it in the code

Source = Excel.CurrentWorkbook(){[Name="[B][COLOR="#FF0000"]Table1[/COLOR][/B]"]}[Content],

for something more post representative source example and expected result

 

[factoryf]

Works great! thanks a bunch

 

[sandy666]

You are welcome