Hello,
I have an interesting dilemma, in the attached PowerBI report I have some invoices that have the freight in the header records and other invoices that have freight in the detail records. I need to somehow get the freight that is in the header records to be in the detail lines. This way I can then filter down the report by product line which will not include freight. At the same time I need the total in the card at the top of the report (labeled in Pink in the attachment) to include freight amounts. Any guidance is appreciated!!
SalesDollarsByProduct-Rework.pbix (2.8 MB)
Hi Preston,
Is this an Event script?
if yes, your should try using oBusObj.Lines to get the freight to the detail lines.
Hi @Preston,
See if this meets your requirement.
- Rename your current AR_InvoiceHistoryHeader to AR_InvoiceHistoryHeaderAll and disable load
- Reference this Query (1) and rename it AR_InvoiceHistoryHeaderFreight
- Filter on records with freight in the header => [FreightAmt] <> 0
- Right-click in your [Key] column header and select “Remove Duplicates”
- Rename the FreightAmt column FreightAmtOld
- Remove the columns “DiscountAmt”, “Dollars”, “AR_InvoiceHistoryDetail.ExtensionAmt”, “ProductLine”
- Add a Custom Column with this logic
#table( type table [FreightAmt=number, DiscountAmt=number, Dollars=number, AR_InvouceHistoryDetail.ExtensionAmt=number, ProductLine=text], {{ 0, 0, [FreightAmtOld], [FreightAmtOld], “Freight” }} )
- Remove the FreightAmtOld column
- Expand the Nested table in the Custom column
- Set correct data types
- Add a manual step to reorder the columns,
Table.ReorderColumns( ChType, Table.ColumnNames( Source ), MissingField.Ignore)
On the Home tab, select Append Queries and select Append Queries as New, the UI will generate this code: Table.Combine({AR_InvoiceHistoryHeaderAll, AR_InvoiceHistoryHeaderFreight})
If you set the FileLocation parameter, all queries will be restored.
Here are the sample files. AR_InvoiceHistoryHeader.csv (17.5 MB)
eDNA - Freight to Line Details.pbix (1.3 MB)
I hope this is helpful.
Hi @Preston, did the response provided by @aaretobi and @melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
Hi @Preston, we’ve noticed that no response has been received from you since the 6th of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Hi @Preston, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.
We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!
Can I ask you where the query is that you are referring too is located? Sorry I am having a hard time following what you want me to do. -Thanks!
In the Power Query Editor, you can get there via Home/Transform Data
In the provided sample you’ll find all queries and steps I refer to. (see Applied Steps pane on the right hand side)
If you have any specific questions, please let me know.
I hope this is helpful.
1 Like
Sorry it took so long to get back to you, this worked perfect!!! Thanks so much for your help!
1 Like
