Columns connecting

[adrian33]

Hi,
I don't know how to fix my data. I want to have 2 columns, first with ID number, and second with link.

Now it looks like that:

I want to add "Issue number_1" to the "Issue number" and the same with images.

How can I do this to keep issue number together with image assigned to this?

[jennratten]

Hello - are you trying to accomplish something like the image below?  If so, this is how you can do it.  If not, please provide an example of the expected result.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUcooKSkottLXLy8v10tMStZLzs8FihrhkInViVYyxqnLBI8uU5y6zHDpigUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [IssueNumber = _t, Image = _t, IssueNumber_1 = _t, Image_1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"IssueNumber", Int64.Type}, {"Image", type text}, {"IssueNumber_1", Int64.Type}, {"Image_1", type text}}),
    TableToColumns = Table.ToColumns( #"Changed Type" ),
    IssueNumbers = List.Combine ( { TableToColumns{0}, TableToColumns{2} } ),
    Images = List.Combine ( { TableToColumns{1}, TableToColumns{3} } ),
    ListColumns = { IssueNumbers, Images },
    TableFromColumns = Table.FromColumns ( ListColumns ),
    #"Sorted Rows" = Table.Sort(TableFromColumns,{{"Column1", Order.Ascending}}),
    #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"Column1", "IssueNumber"}, {"Column2", "Image"}})
in
    #"Renamed Columns"

[adrian33]

yes, something like that 🙂

[jennratten · Accepted solution]

This is how you can get it all into one column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUcooKSkottLXLy8v10tMStZLzs8FihrhkInViVYyxqnLBI8uU5y6zHDpigUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [IssueNumber = _t, Image = _t, IssueNumber_1 = _t, Image_1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"IssueNumber", Int64.Type}, {"Image", type text}, {"IssueNumber_1", Int64.Type}, {"Image_1", type text}}),
    TableToColumns = Table.ToColumns( #"Changed Type" ),
    IssueNumbers = List.Combine ( { TableToColumns{0}, TableToColumns{2} } ),
    Images = List.Combine ( { TableToColumns{1}, TableToColumns{3} } ),
    ListColumns = { IssueNumbers, Images },
    TableFromColumns = Table.FromColumns ( ListColumns ),
    #"Sorted Rows" = Table.Sort(TableFromColumns,{{"Column1", Order.Ascending}}),
    #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"Column1", "IssueNumber"}, {"Column2", "Image"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each "ImageNumber " & Number.ToText ( [IssueNumber] ) & " " & [Image], type text)
in
    #"Added Custom"

[mussaenda]

Hi @adrian33 ,

add means you need the sum per row? like issue + issue 1?

[adrian33]

No, I mean that I want to have issue 1 in the same column, but as a seperate value.

Something like:
issue x

issue y
issue_1 x
issue z
issue_1 y