Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |