Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
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.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
98 | |
79 | |
65 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |