• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

How to manage imported and distributed items|Power Query|Excel Forum|My Online Training Hub

You are here: Home / How to manage imported and distributed items|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryHow to manage imported and distribu…
sp_PrintTopic sp_TopicIcon
How to manage imported and distributed items
Avatar
Corrie Hiepner
Durban, South Africa
Member
Members
Level 0
Forum Posts: 7
Member Since:
January 10, 2020
sp_UserOfflineSmall Offline
1
August 18, 2020 - 6:14 pm
sp_Permalink sp_Print

Hi, I need some assistance please.

We import products that are then distributed to customers.  The list that is currently used (see attached example), shows the quantity imported (Qty) and the duty paid (Duty).

When Items are sold (Used), it is indicated on the sheet by adding a row with the status of Used.  All Used items must be equal to the Imported items based on Line number, Product code, Invoice Number, BE date and BE number. The distributed (Used) items are not always equal to the Import qty, and it might take 2 or more lines to match up to the Import quantity.

For row 13 there were 560 items imported, but nothing used or distributed yet.

Products must be distributed (Used) chronologically, so first in, first out.

The problem is that this list can consist of 7 000 or more rows and to find the next available product becomes a challenge.

Is there a way to perhaps filter out/remove from view, where the Import and Used lines add up to the same quantity for the same Line Number, Product Code, Duty, Invoice number, Customs BE date, Customer BE Number and Qty?

We also need to calculate the pro-rata duty for the Used items (see Column E > used items only)

Any assistance on how to manage this in Power Query would be greatly appreciated.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
August 19, 2020 - 9:25 pm
sp_Permalink sp_Print

Hi Corrie,

The FIFO problem is more complicated than you described, for example, units sold may take prices from 2 imported items, if the quantity remaining from purchase is not enough to fill the order, so the rest of the quantity needs to be taken from the next purchase.

Try this link: https://community.powerbi.com/.....d-p/312235

At the end of the topic you will find a download, hope it will work for your situation.

Avatar
Corrie Hiepner
Durban, South Africa
Member
Members
Level 0
Forum Posts: 7
Member Since:
January 10, 2020
sp_UserOfflineSmall Offline
3
August 20, 2020 - 10:48 pm
sp_Permalink sp_Print

Hi Catalin,

You're quite right, it is more involved.

Thank you for the shared link.  I will have a look and see how I can apply it in our scenario.

Take care!

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Kim Knox, Kevin Lenahan, Mitchell Dahms, michael serna, Kathryn Patton
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 204
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Kathryn Patton
Maria Conatser
Jefferson Granemann
Glen Coulthard
Nikki Fox
Rachele Dickie
Raj Mattoo
Mark Luke
terimeri dooriyan
Jack Aston
Forum Stats:
Groups: 3
Forums: 24
Topics: 6221
Posts: 27285

 

Member Stats:
Guest Posters: 49
Members: 31909
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.