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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
EduardoM83
New Member

Returning multiple values from a column to a new table

Hi,

Very new to PowerBI, I have a question around returning mutliple values from a table based on a single value in another.

Example is:

Table 1

Product IDProduct NameQuantity
1234Stapler 11

Table 2

Product IDProduct NameComponentsQuantity
2345Stapler 2Stapler v21
  Staple Pack x1002
    
4567Stapler 3Stapler v3 1
  Staple Pack x 502
    
1234Stapler 1Stapler v11
  Staple Pack x 253
  Staple Remover1

I can use the Product ID in both Tables as a join, but I dont know how I can add a column into Table 1 so that it would then show all the components from Table 2 against the matching Product ID?

 

Hoping this is easy but any guidance would be great.

1 ACCEPTED SOLUTION

@EduardoM83,

 

you have to edit Table1 and Table2 step. Delete whole code and replace it with your Table1 reference, i.e. if your Table1 reference is MyTable1 then replace this whole code with = MyTable1 (if you have some special characters or space in table name i.e My Table1, you have to write it like this: #"My Table1"

 

Do the same for Table2.

 

dufoq3_0-1707746841937.png

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

4 REPLIES 4
EduardoM83
New Member

Hi Dufoq3,

 

Thank you for taking the time to put this together. I am going to have a look at it hopefully get it working.

Thanks.

dufoq3
Super User
Super User

Hi @EduardoM83,

 

you should use Merge Queries UI button, but before that you have to do some additional steps. You can check that steps here (in this example both tables created in same query). You will be probably confused - but you can click on every single step and try to understand. BTW. Table1 and Table2 steps (based on JSON) - it is just a raw data which I created via UI buddon Enter Data.

I hope this will help you. (Don't spend time with Table2_ReplaceValue step please. At this moment you will probably don't understand how it works).

let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUQouSSzISS1SMASyDZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product ID" = _t, #"Product Name" = _t, Quantity = _t]),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjI2MVXSUQouSSzISS1SMEJil4E4hkqxOtFKCkCWAlxKISAxOVuhwtDAAChkhKIAgkEiJqZm5kiGGSMbbKyA32QFU3wmGwLdjGSaIbLJhgQMNgJ51hibgqDU3Pyy1CKI/lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product ID" = _t, #"Product Name" = _t, Components = _t, Quantity = _t]),
    Table2_ReplacedValue = Table.ReplaceValue(Table2,
    null,
    null,
    (x,y,z)=> if Text.Trim(x) = "" then null else x,
{"Product ID", "Product Name", "Components", "Quantity"}),
    Table2_FilledDown = Table.FillDown(Table2_ReplacedValue,{"Product ID"}),
    #"Merged Queries" = Table.NestedJoin(Table1, {"Product ID"}, Table2_FilledDown, {"Product ID"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Product Name", "Components", "Quantity"}, {"Table2.Product Name", "Table2.Components", "Table2.Quantity"})
in
    #"Expanded Table2"

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Hi Dufoq3,

 

I managed to get this to work which is great, but if I add an additional row in table 1, lets say for Product 2345, it doesnt add that to the edited table. Is that possible?

Thanks again.

@EduardoM83,

 

you have to edit Table1 and Table2 step. Delete whole code and replace it with your Table1 reference, i.e. if your Table1 reference is MyTable1 then replace this whole code with = MyTable1 (if you have some special characters or space in table name i.e My Table1, you have to write it like this: #"My Table1"

 

Do the same for Table2.

 

dufoq3_0-1707746841937.png

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors