# PowerQuery: Row with same ID, CONCATENATE image column into one row.



## santnok (Sep 26, 2019)

Hi

In PowerQuery I have a tabel that looks like the tabel under (*TBL1*). if you see under the ItemNo I have 1 or several same ID. So I need help to
make a colum that takes e.g. ItemNo: SHD3061 that have 2 rows of images and concatenate. So my result I hope to achieve is this (*TBL2*)

*TBL2*

ItemNoImagesSHD3061/photos/SHD3061/SHD3061.jpg, /photos/SHD3061/SHD3061_4.png

<tbody>

</tbody>
From the tabel under you can see that the Image can be more than 1 for a ItemNo.

*TBL1: *Here is my result today I hope to fix 

ItemNoImagesSHD3061/photos/SHD3061/SHD3061.jpgSHD3061/photos/SHD3061/SHD3061_4.pngSHD3062/photos/SHD3062/SHD3062.jpgSHD3062/photos/SHD3062/SHD3062_pack.pngSHD3063/photos/SHD3063/SHD3063.jpgSHD3063/photos/SHD3063/SHD3063_4.pngSHD3071/photos/SHD3071/SHD3071.jpgSHD3071/photos/SHD3071/SHD3071_1.pngSHD3072/photos/SHD3072/SHD3072.jpgSHD3072/photos/SHD3072/SHD3072_1.pngSHD3361/photos/SHD3361/SHD3361.jpgSHD3361/photos/SHD3361/SHD3361_4.pngSHD3362/photos/SHD3362/SHD3362.jpgSHD3362/photos/SHD3362/SHD3362_pack.pngSHD3461/photos/SHD3461/SHD3461.jpgSHD3461/photos/SHD3461/SHD3461_4.pngSHD3462/photos/SHD3462/SHD3462.jpgSHD3462/photos/SHD3462/SHD3462_pack.pngSHD3561/photos/SHD3561/SHD3561.jpgSHD3561/photos/SHD3561/SHD3561_4.pngSHD3562/photos/SHD3562/SHD3562.jpgSHD3562/photos/SHD3562/SHD3562_4.pngSHD3661/photos/SHD3661/SHD3661.jpgSHD3661/photos/SHD3661/SHD3661_4.pngSHD3662/photos/SHD3662/SHD3662.jpgSHD3662/photos/SHD3662/SHD3662_pack.pngSHD3862/photos/SHD3862/SHD3862.jpgSHD3862/photos/SHD3862/SHD3862_Box.pngSHD3863/photos/SHD3863/SHD3863.jpgSHD3863/photos/SHD3863/SHD3863_Box.pngSWB7140/photos/SWB7140/SWB7140.jpgSWB7140/photos/SWB7140/SWB7140_0.jpgSWB7140/photos/SWB7140/SWB7140_1.jpgSWB7140/photos/SWB7140/SWB7140_10.jpgSWB7140/photos/SWB7140/SWB7140_11.jpgSWB7140/photos/SWB7140/SWB7140_12.jpgSWB7140/photos/SWB7140/SWB7140_13.jpgSWB7140/photos/SWB7140/SWB7140_14.jpgSWB7140/photos/SWB7140/SWB7140_15.jpg

<tbody>

</tbody>
Hope it is possible to do without coding directly in the Advanced Editor

Hope somone can help me out here

Thanks for all help


----------



## sandy666 (Sep 26, 2019)

try


```
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"ItemNo"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Images", each Table.Column([Count],"Images")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Images", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
in
    #"Extracted Values"[/SIZE]
```

without Advanced Editor


----------



## santnok (Sep 26, 2019)

Thanks for the reply, can you please help me out when I have this code before in Advanced Editor
How to place your code in my code 


```
let
    Kilde = Xml.Tables(Web.Contents("https://xxxxxxxxxxxxxxxxxxx.xml")),
    Table1 = Kilde{1}[Table],
    Table0 = Table1{0}[Table],
    #"Utvidet Picture" = Table.ExpandTableColumn(Table0, "Picture", {"Property"}, {"Property"}),
    #"Utvidet Property" = Table.ExpandTableColumn(#"Utvidet Picture", "Property", {"Attribute:Value"}, {"Attribute:Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Utvidet Property",{{"Attribute:Value", "Image"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"ItemNo", "Image"})
in
    #"Removed Other Columns"
```

I see that in the code it shows some norwegian words.

Kilde = Source
Utvidet = Extended

Thanks


----------



## sandy666 (Sep 26, 2019)

if the result of your M-code is your table from post#1 add last 3 lines from my post to your M-code, eg.


```
[SIZE=1]let
    Kilde = Xml.Tables(Web.Contents("https://xxxxxxxxxxxxxxxxxxx.xml")),
    Table1 = Kilde{1}[Table],
    Table0 = Table1{0}[Table],
    #"Utvidet Picture" = Table.ExpandTableColumn(Table0, "Picture", {"Property"}, {"Property"}),
    #"Utvidet Property" = Table.ExpandTableColumn(#"Utvidet Picture", "Property", {"Attribute:Value"}, {"Attribute:Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Utvidet Property",{{"Attribute:Value", "Image"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"ItemNo", "Image"}),
[COLOR="#0000FF"]    #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"ItemNo"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Images", each Table.Column([Count],"Images")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Images", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
in
    #"Extracted Values"[/COLOR][/SIZE]
```

but you should check proper syntax (you need to use Advanced Editor    )

or
with your M-code
- group by ItemNo
- add custom column with Table.Column(....
- expand List column with delimiter: ", " (second option)

btw. your code has *Image* but your table from post#1 has *Images*


----------



## santnok (Sep 26, 2019)

Hi

Thanks, now I get an Error

See the picture here http://servyou.net/Capture.PNG

My code is now


```
let
    Kilde = Xml.Tables(Web.Contents("https://xxxxxxxxxxxx.xml")),
    Table1 = Kilde{1}[Table],
    Table0 = Table1{0}[Table],
    #"Utvidet Picture" = Table.ExpandTableColumn(Table0, "Picture", {"Property"}, {"Property"}),
    #"Utvidet Property" = Table.ExpandTableColumn(#"Utvidet Picture", "Property", {"Attribute:Value"}, {"Attribute:Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Utvidet Property",{{"Attribute:Value", "Image"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"ItemNo", "Image"}),
    #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"ItemNo"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Images", each Table.Column([Count],"Images")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Images", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
in
    #"Extracted Values"
```

Thanks for help


----------



## sandy666 (Sep 26, 2019)

as I said: check syntax (Image vs Image*s*)


----------



## santnok (Sep 26, 2019)

Hi again, 

I found what is wrong, see in your code that you have writen *Images*, I changed to *Image* and it work 
So thank you verry much for the help  Have a nice day 

Sorry, dident see btw in your last post


----------



## sandy666 (Sep 26, 2019)

you are welcome


----------



## santnok (Oct 11, 2019)

Hi again

Have a follwup question. If I want to seperate the images into new column for each image insted of have them comma seperated in one column, is that possible?

Best Regard 
Roy


----------



## sandy666 (Oct 11, 2019)

santnok said:


> I want to seperate the images into new column for each image insted of have them comma seperated in one column



from the ribbon use *Split Column* with delimiter ", " without quotation marks


----------



## santnok (Sep 26, 2019)

Hi

In PowerQuery I have a tabel that looks like the tabel under (*TBL1*). if you see under the ItemNo I have 1 or several same ID. So I need help to
make a colum that takes e.g. ItemNo: SHD3061 that have 2 rows of images and concatenate. So my result I hope to achieve is this (*TBL2*)

*TBL2*

ItemNoImagesSHD3061/photos/SHD3061/SHD3061.jpg, /photos/SHD3061/SHD3061_4.png

<tbody>

</tbody>
From the tabel under you can see that the Image can be more than 1 for a ItemNo.

*TBL1: *Here is my result today I hope to fix 

ItemNoImagesSHD3061/photos/SHD3061/SHD3061.jpgSHD3061/photos/SHD3061/SHD3061_4.pngSHD3062/photos/SHD3062/SHD3062.jpgSHD3062/photos/SHD3062/SHD3062_pack.pngSHD3063/photos/SHD3063/SHD3063.jpgSHD3063/photos/SHD3063/SHD3063_4.pngSHD3071/photos/SHD3071/SHD3071.jpgSHD3071/photos/SHD3071/SHD3071_1.pngSHD3072/photos/SHD3072/SHD3072.jpgSHD3072/photos/SHD3072/SHD3072_1.pngSHD3361/photos/SHD3361/SHD3361.jpgSHD3361/photos/SHD3361/SHD3361_4.pngSHD3362/photos/SHD3362/SHD3362.jpgSHD3362/photos/SHD3362/SHD3362_pack.pngSHD3461/photos/SHD3461/SHD3461.jpgSHD3461/photos/SHD3461/SHD3461_4.pngSHD3462/photos/SHD3462/SHD3462.jpgSHD3462/photos/SHD3462/SHD3462_pack.pngSHD3561/photos/SHD3561/SHD3561.jpgSHD3561/photos/SHD3561/SHD3561_4.pngSHD3562/photos/SHD3562/SHD3562.jpgSHD3562/photos/SHD3562/SHD3562_4.pngSHD3661/photos/SHD3661/SHD3661.jpgSHD3661/photos/SHD3661/SHD3661_4.pngSHD3662/photos/SHD3662/SHD3662.jpgSHD3662/photos/SHD3662/SHD3662_pack.pngSHD3862/photos/SHD3862/SHD3862.jpgSHD3862/photos/SHD3862/SHD3862_Box.pngSHD3863/photos/SHD3863/SHD3863.jpgSHD3863/photos/SHD3863/SHD3863_Box.pngSWB7140/photos/SWB7140/SWB7140.jpgSWB7140/photos/SWB7140/SWB7140_0.jpgSWB7140/photos/SWB7140/SWB7140_1.jpgSWB7140/photos/SWB7140/SWB7140_10.jpgSWB7140/photos/SWB7140/SWB7140_11.jpgSWB7140/photos/SWB7140/SWB7140_12.jpgSWB7140/photos/SWB7140/SWB7140_13.jpgSWB7140/photos/SWB7140/SWB7140_14.jpgSWB7140/photos/SWB7140/SWB7140_15.jpg

<tbody>

</tbody>
Hope it is possible to do without coding directly in the Advanced Editor

Hope somone can help me out here

Thanks for all help


----------



## santnok (Oct 11, 2019)

Thanks


----------



## sandy666 (Oct 11, 2019)

welcome


----------

