Create and Expand the column with all records.

Question

Hi,As a newbie to Power BI, requesting help.&nbsp;I am having a dataset like below,&nbsp;IDLOCATIONPACKAGE1234ABCBASE4567DEFGOLD7900GHISILVER&nbsp;11233JKLPLATINUM&nbsp;14566MNOPLATINUM&nbsp;17899PQRGOLD21232STUSILVER&nbsp;The list goes&nbsp;up to a million rows, but every package has different numbers of products in them. Like below.&nbsp;PKGPRODSMANUFACTURER&nbsp;PKGPRODSMANUFACTURER&nbsp;PKGPRODSMANUFACTURER&nbsp;PKGPRODSMANUFACTURERBASE11XYZ&nbsp;SILVER&nbsp;11XYZ&nbsp;GOLD&nbsp;11XYZ&nbsp;PLATINUM&nbsp;11XYZBASE12XYZ&nbsp;SILVER&nbsp;12XYZ&nbsp;GOLD&nbsp;12XYZ&nbsp;PLATINUM&nbsp;12XYZBASE13XYZ&nbsp;SILVER&nbsp;13XYZ&nbsp;GOLD&nbsp;13XYZ&nbsp;PLATINUM&nbsp;13XYZBASE14XYZ&nbsp;SILVER&nbsp;14XYZ&nbsp;GOLD&nbsp;14XYZ&nbsp;PLATINUM&nbsp;14XYZBASE15GHY&nbsp;SILVER&nbsp;15GHY&nbsp;GOLD&nbsp;15GHY&nbsp;PLATINUM&nbsp;15GHYBASE16GHY&nbsp;SILVER&nbsp;16GHY&nbsp;GOLD&nbsp;16GHY&nbsp;PLATINUM&nbsp;16GHYBASE17GHY&nbsp;SILVER&nbsp;17GHY&nbsp;GOLD&nbsp;17GHY&nbsp;PLATINUM&nbsp;17GHYBASE18QWE&nbsp;SILVER&nbsp;18QWE&nbsp;GOLD&nbsp;18QWE&nbsp;PLATINUM&nbsp;18QWEBASE19QWE&nbsp;SILVER&nbsp;19QWE&nbsp;GOLD&nbsp;19QWE&nbsp;PLATINUM&nbsp;19QWE&nbsp;&nbsp;&nbsp;&nbsp;SILVER&nbsp;29XYZ&nbsp;GOLD&nbsp;29XYZ&nbsp;PLATINUM&nbsp;29XYZ&nbsp;&nbsp;&nbsp;&nbsp;SILVER&nbsp;30XYZ&nbsp;GOLD&nbsp;30XYZ&nbsp;PLATINUM&nbsp;30XYZ&nbsp;&nbsp;&nbsp;&nbsp;SILVER&nbsp;31XYZ&nbsp;GOLD&nbsp;31XYZ&nbsp;PLATINUM&nbsp;31XYZ&nbsp;&nbsp;&nbsp;&nbsp;SILVER&nbsp;32QWE&nbsp;GOLD&nbsp;32QWE&nbsp;PLATINUM&nbsp;32QWE&nbsp;&nbsp;&nbsp;&nbsp;SILVER&nbsp;33GHY&nbsp;GOLD&nbsp;33GHY&nbsp;PLATINUM&nbsp;33GHY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;GOLD&nbsp;64KLJ&nbsp;PLATINUM&nbsp;64KLJ&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;GOLD&nbsp;65XYZ&nbsp;PLATINUM&nbsp;65XYZ&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;GOLD&nbsp;66XYZ&nbsp;PLATINUM&nbsp;66XYZ&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;GOLD&nbsp;67QWE&nbsp;PLATINUM&nbsp;67QWE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;GOLD&nbsp;68KLJ&nbsp;PLATINUM&nbsp;68KLJ&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;GOLD&nbsp;69KLJ&nbsp;PLATINUM&nbsp;69KLJ&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;PLATINUM&nbsp;100XYZ&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;PLATINUM&nbsp;101QWE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;PLATINUM&nbsp;102GHY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;PLATINUM&nbsp;103KLJ&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;PLATINUM&nbsp;104XYZI am unable to create the relationship between two tables because&nbsp;PBI says it must have some unique values.&nbsp;I need the count of every product and count of every package through which this product is being delivered.&nbsp;&nbsp;How to do it?I was thinking about generating a custom column where all the products shall be 'tabled' along with other details and then expanding it but don't know how to do it.Will it do the trick?&nbsp;Please help me with it.&nbsp;&nbsp;&nbsp;

v-caliao-msft · Answer

bipinbhaskarwar,
&nbsp;
I am unable to create the relationship between two tables because&nbsp;PBI says it must have some unique values.
Currently Power BI not support many to many relationship directly. So when creating relationships, make sure that at least in one of the tables the value in your table are unique.&nbsp;
&nbsp;
Besides, you need to re-shape you table so that you can count the measure you want.

Sample Measure:
Measure = CALCULATE(COUNTA(Table1[PKG]),FILTER(Table1,Table1[PKG]="BASE"))
&nbsp;
Regards,
Chalrie Liao

bipinbhaskarwar · Answer

Thanks for the suggestion.Actually, i get the data of package-product table exactly as you have reshaped.&nbsp;The problem is getting the&nbsp;counts of products and counts of product through the certain package.I am not sure how to get it.&nbsp;Thanks though.&nbsp;

Forum Discussion

Create and Expand the column with all records.

2 Replies

Related content

Expand list with records to columns

Dynamically Expand Table Records

Expand records to columns

Expanding JSON Record as Row

Table Merge creates duplicate records

ID	LOCATION	PACKAGE
1234	ABC	BASE
4567	DEF	GOLD
7900	GHI	SILVER
11233	JKL	PLATINUM
14566	MNO	PLATINUM
17899	PQR	GOLD
21232	STU	SILVER