Columns data to Rows

[mittulburad]

Hello,

I am new to Power BI. Below are three data tables.

Table 1 - VchHdr

VoucherID	Date	VoucherTypeName	VoucherNo	DestinationGodown
451	01-04-2020 00:00	Cutting	1	JKBM Godown

Table 2 - InvLine

VoucherID	AccLineNo	InvLineNo	StockItemName	IsDeemedPositive	IsAutoNegate	Rate	Amount	ActualQuantity	ActualUOM
451	0	1	Club Deluxe - Cut Tas	TRUE	FALSE	12.16	-120384	9900	Pcs
451	0	2	Seconds Cutting Tas	TRUE	FALSE	7	-350	50	Pcs
451	0	3	Wastage Cutting Tas	TRUE	FALSE	0	0	25	Pcs
451	0	4	Seconds Dori Cutting Tas	TRUE	FALSE	7	-175	25	Pcs
451	0	5	Club Deluxe- WIP	FALSE	FALSE	10.16	101600	10000	Nos

Table 3 - BatchLine

VoucherId	AccLineNo	InvLineNo	BatchLineNo	MfdOn	GodownName	BatchName	DestinationGodownName	ActualUOM
451	0	1	1	01-01-1900 00:00	JKBM Godown	Primary Batch	JKBM Godown	Pcs
451	0	2	1	01-01-1900 00:00	JKBM Godown	Primary Batch	JKBM Godown	Pcs
451	0	3	1	01-01-1900 00:00	JKBM Godown	Primary Batch	JKBM Godown	Pcs
451	0	4	1	01-01-1900 00:00	JKBM Godown	Primary Batch	JKBM Godown	Pcs
451	0	5	1	16-03-2020 00:00	JKBM Godown	J1451	JKBM Godown	Nos

Below is the final result table i need:

Date	StockItemName	InvLineNo 1	InvLineNo 2	InvLineNo 3	InvLineNo 4	Total	Lot No.
01-04-2020 00:00	Club Deluxe- WIP	9900	50	25	25	10000	J1451

Note: 1) StockItemName where the Column "IsDeemedPositive" yes in InvLine Table

2) Lot no. will be from Table BatchLine where ActualUOM is Nos

Thanks

[Anonymous]

You can use the merge function in the query editor (Home > Transform Data) to merge these tables.

You can also apply filters ( "IsDeemedPositive" yes in InvLine Table)

Although Looking at your data it is a little unclear how exactly you want to aggregate these rows. You'll need to pick a column to merge on for each join. If you post an example PBIX I can take a look.

[mittulburad]

Hello, Let assume there is only one table (Table 2 - InvLine)

I want the below final output:-

VoucherID	StockItemName	1	2	3	4	Total
451	Club Deluxe - WIP	9900	50	25	25	10000

When "IsDeemedPositive" FALSE - it will appear in in Column "StockItemName" 

and the Actual Qty of the other rows with same VoucherID will appear in the values field against that StockItemName.

Hope this will help.

[Anonymous]

OK cool - if you go back to your example with multiple tables, heres what you can do to achieve that effect:

If you want to maintain your first table, duplicate it and apply this step to the duplicate otherwise just filter the table to only show rows where the "IsDeemedPositive" is FALSE. Now you have a table with only the items you are interested in.

Then merge the second table (your table with Batchname in) into the first one, joining on the voucher ID. Once you do this you'll get a column appear at the end of the first table, with each value being the word Table in yellow. Click the two arrows at the top of the column and you should see two options, expand or aggregate. If you click aggregate you'll see the options to aggregate the second table into the first - then you can count items, or sum quantity, whatever you need to do.

Hope that helps!