Power Query: Split table column with multiple cells in the same row

0
votes

I have a SharePoint list as a datasource in Power Query.

It has a "AttachmentFiles" column, that is a table, in that table i want the values from the column "ServerRelativeURL".

enter image description here

I want to split that column so each value in "ServerRelativeURL"gets its own column.

enter image description here

I can get the values if i use the expand table function, but it will split it into multiple rows, I want to keep it in one row.

enter image description here

I only want one row per unique ID.

Example:

enter image description here

I can live with a fixed number of columns as there are usually no more than 3 attachments per ID.

I'm thinking that I can add a custom column that refers to "AttachmentFiles ServerRelativeURL Value(1)" but I don't know how.

Can anybody help?

2
excelpowerquerysharepoint-list

2 Answers

0
votes

Try this code:

let
    fn = (x)=> {x, #table({"ServerRelativeUrl"},List.FirstN(List.Zip({{"a".."z"}}), x*2))},
    Source = #table({"id", "AttachmentFiles"},{fn(2),fn(3),fn(1)}),
    replace = Table.ReplaceValue(Source,0,0,(a,b,c)=>a[ServerRelativeUrl],{"AttachmentFiles"}),
    cols = List.Transform({1..List.Max(List.Transform(replace[AttachmentFiles], List.Count))}, each "url"&Text.From(_)),
    split = Table.SplitColumn(replace, "AttachmentFiles", (x)=>List.Transform({0..List.Count(x)-1}, each x{_}), cols)
in
    split

enter image description here

0
votes

I manged to solve it myself.

I added 3 custom columns like this CustomColumn1: [AttachmentFiles]{0} CustomColumn2: [AttachmentFiles]{1} CustomColumn3: [AttachmentFiles]{2}

And expanded them with only the "ServerRelativeURL" selected. It would be nice to have a dynamic solution. But this will work fine for now.