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 )

sku	pic
BM1TCBK	https://www.catalograck.com/ImgVDHR/NFA/BM1TCBK.jpg
BM1TCBK-TX	https://www.catalograck.com/ImgVDHR/NFA/BM1TCBK.jpg
BM1TCBL	https://www.catalograck.com/ImgVDHR/NFA/BM1TCBL.jpg
BM1TCBL-TX	https://www.catalograck.com/ImgVDHR/NFA/BM1TCBL.jpg
BM1TCRD	https://www.catalograck.com/ImgVDHR/NFA/BM1TCR.jpg
BM1TCRD-TX	https://www.catalograck.com/ImgVDHR/NFA/BM1TCR.jpg
BM1TSBK	https://www.catalograck.com/ImgVDHR/NFA/BM1TSBK-1.jpg
BM1TSBL	https://www.catalograck.com/ImgVDHR/NFA/BM1TSBL-1.jpg

<colgroup><col><col></colgroup><tbody>
</tbody>

here is how i get the data

D023LH	https://www.catalograck.com/ImgVDHR/NFA/D023LH-1.jpg
D023LH	https://www.catalograck.com/ImgVDHR/NFA/D023LH-2.jpg
D023LH	https://www.catalograck.com/ImgVDHR/NFA/D023LH-4.jpg
D023LH	https://www.catalograck.com/ImgVDHR/NFA/D023LH-5.jpg

<colgroup><col><col></colgroup><tbody>
</tbody>

here is how i need the data

part number	pic1	pic2	pic3	pic4
D023LH	https://www.catalograck.com/ImgVDHR/NFA/D023LH-1.jpg	https://www.catalograck.com/ImgVDHR/NFA/D023LH-2.jpg	https://www.catalograck.com/ImgVDHR/NFA/D023LH-4.jpg	https://www.catalograck.com/ImgVDHR/NFA/D023LH-5.jpg

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>

thanks in advance

 

[sandy666]

with PowerQuery (Get&Transform)

SKU	Pic		SKU	Pic.1	Pic.2	Pic.3	Pic.4
D023LH	https://www.catalograck.com/ImgVDHR/NFA/D023LH-1.jpg		D023LH	https://www.catalograck.com/ImgVDHR/NFA/D023LH-1.jpg	https://www.catalograck.com/ImgVDHR/NFA/D023LH-2.jpg	https://www.catalograck.com/ImgVDHR/NFA/D023LH-4.jpg	https://www.catalograck.com/ImgVDHR/NFA/D023LH-5.jpg
D023LH	https://www.catalograck.com/ImgVDHR/NFA/D023LH-2.jpg
D023LH	https://www.catalograck.com/ImgVDHR/NFA/D023LH-4.jpg
D023LH	https://www.catalograck.com/ImgVDHR/NFA/D023LH-5.jpg

[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