LookUpValue One to Many issue convert to columns

[mgirou]

Hello,

I am new to Power BI so please bare with me.

I have a Two tables:

First table has a list of ID's

ID

4

26

28

Second table has a list of ID's and File ID's

ID     FileID

4      15220

4      15219

4      15217

26    15578

26    14768

I want to be able to make the first table look like this (FYI Maximum of 6 file ID's)

ID     FileID1     FileID2     FileID3     FileID4     FileID5      FileID6

4      15220      15219     15217

26    15578      14768

I am really not sure about how to approach this. (either Lookupvalue or somehweres in the query editor) OR if this is even possible?

Any help would be appreciated.

Thanks

[MarcelBeug · Accepted solution]

It can be done with Power Query.

Create a new query, go to the advanced editor and replace the default code by the code below.

Prerequisite is that both tables already exist.

let
    // Merge both tables on ID, Expand nested FileID table
    Source = Table.NestedJoin(IDs,{"ID"},FileIDs,{"ID"},"NewColumn",JoinKind.LeftOuter),
    ExpandedFileID = Table.ExpandTableColumn(Source, "NewColumn", {"FileID"}, {"FileID"}),

    // Group on ID in order to create Headers "FileID1", "FileID2", etc.
    GroupedOnID = Table.Group(ExpandedFileID, {"ID"}, {{"AllData", each _, type table}}),

    // Add Index to nested table as basis for Headers
    AddedIndex = Table.TransformColumns(GroupedOnID,{{"AllData", each Table.AddIndexColumn(_, "Header", 1, 1)}}),

    // Expand FileID and Header from nested table
    ExpandedAllData = Table.ExpandTableColumn(AddedIndex, "AllData", {"FileID", "Header"}, {"FileID", "Header"}),
    
    // Add prefix "FileID" to Header (1,2, etc.)
    AddedPrefix = Table.TransformColumns(ExpandedAllData, {{"Header", each "FileID" & Text.From(_, "en-US"), type text}}),

    // Pivot the Header column (with advanced option: don't summarize).
    // The list of 6 column headers is obtained wih adjusted code: List.Transform({1..6}, each "FileID"&Text.From(_))
    PivotedOnHeader = Table.Pivot(AddedPrefix, List.Transform({1..6}, each "FileID"&Text.From(_)), "Header", "FileID"),

    // Remove rows with FileID1 = null
    FilteredRows = Table.SelectRows(PivotedOnHeader, each ([FileID1] <> null))
in
    FilteredRows