Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mgirou
Helper II
Helper II

LookUpValue One to Many issue convert to columns

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

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

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
Specializing in Power Query Formula Language (M)

View solution in original post

1 REPLY 1
MarcelBeug
Community Champion
Community Champion

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
Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.