Forum Discussion
How can I create multiple rows from two columns in Power Bi Power Query
I am trying deperately trying to get my head around Power Query, and I must say I'm regretting my decission.
I need to create an inverted list of if something did not happen in a moth over a specific time, problem is that I need to collect multuple data points, so I have two tables that looks like
Table A
"Unit ID","Name",Location"
123, Test, 2nd Floor
321, Test2, 3rd Floor
Table B
"Link ID", "Name"
567, Tryer
765, Tried
I now need power query to step through the Unit IDs in the cirst table, and for each one create two rows based on the second table, so I end up with a table that looks like:
Unit ID, Link ID
123, 567
123, 756
321, 567
321, 765
But I can't even begin to figure out how to get it to react. If I would be writing it in a script lanudage, I would write something like
#Table A(UnitId).forEach(CurrentUnitId => {
Table B(Link Id).forEach(CurrentLinkId => {
OutputTable.addRow({CurrentUnitId},{CurrentLinkId})
})
})
I know that is not how its done, but I have no clue how to evenr start, and I've looked around on a lot of videos, but seems like I'm missing something obvious.
Hi Anonymous ,
*EDIT* Apologies, I see that my example used [Unit ID] from the second table, not [Link ID]. It should have been:
= tableB[Link ID]Please follow below for full implementation:
Given table B (benTableB):
// benTableB let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjUzV9JRCimqTC1SitWJVjI3MwXzM1NTlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Link ID" = _t, Name = _t]) in SourceThen table A is as follows:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRCkktLgFSRnkpCm45+flFSrE60UrGRoZQKSMgbVwEl4sFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unit ID" = _t, Name = _t, Location = _t]), addLinkID = Table.AddColumn(Source, "Link ID", each benTableB[Link ID]), expandLinkID = Table.ExpandListColumn(addLinkID, "Link ID"), remOthCols = Table.SelectColumns(expandLinkID,{"Unit ID", "Link ID"}) in remOthColsWith the following output:
Pete
4 Replies
Hi Anonymous ,
Create a new custom column in your first table with the following calculation:
= secondTable[Unit ID]This will create a crossjoin which you can expand using the button at the top of your new column.
Pete
Hi Anonymous ,
*EDIT* Apologies, I see that my example used [Unit ID] from the second table, not [Link ID]. It should have been:
= tableB[Link ID]Please follow below for full implementation:
Given table B (benTableB):
// benTableB let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjUzV9JRCimqTC1SitWJVjI3MwXzM1NTlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Link ID" = _t, Name = _t]) in SourceThen table A is as follows:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRCkktLgFSRnkpCm45+flFSrE60UrGRoZQKSMgbVwEl4sFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unit ID" = _t, Name = _t, Location = _t]), addLinkID = Table.AddColumn(Source, "Link ID", each benTableB[Link ID]), expandLinkID = Table.ExpandListColumn(addLinkID, "Link ID"), remOthCols = Table.SelectColumns(expandLinkID,{"Unit ID", "Link ID"}) in remOthColsWith the following output:
Pete