• 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

Need to edit some data before Combine 2 Data source|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Need to edit some data before Combine 2 Data source|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 QueryNeed to edit some data before Combi…
sp_PrintTopic sp_TopicIcon
Need to edit some data before Combine 2 Data source
Avatar
Justin Redua
Member
Members
Level 0
Forum Posts: 22
Member Since:
February 18, 2022
sp_UserOfflineSmall Offline
1
June 20, 2022 - 6:37 pm
sp_Permalink sp_Print

Dear all,

Hello i'm using microsoft office 365 and windows and I have some problem.

1. I have 2 file that need to combine using power query 

    a. 'File Weekly' is a key information from customer that update weekly.

    b. 'WIP' is our internal file that contain status output for each section.

    c. 'File Result' is the combined result file I want to use

 how to combine it ? I've tried several ways and still having problems. 

2. since the File Weekly is update with different file and different PO number, how can just refresh the data without doing combine it each week ?

3. PO number from WIP is a export file from a system, and somehow the 0 number on front each PO number is gone.

so there is a rule for adding the 0 number (each PO must contain 10 digit number or combination with letter)

    a. if there is alphabet (e.g A,B or C), no need using 0

    b. if the 1st number is 6, it must add 00 before 6.

    c. if the 1st number is 1,2 or 3, it must add 0 before the 1st number

how to change the PO Number on WIP while doing combine using query ? 

4. it's okay to add extra column after finish combine using power query like notes or remark ?

 

below is a link for example file

https://drive.google.com/drive.....sp=sharing

 

Kind Regards,

Justin R

sp_AnswersTopicSeeAnswer See Answer
Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 439
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
2
June 20, 2022 - 7:49 pm
sp_Permalink sp_Print

Google drive wouldn't let me download the file so I replicated it by copying and pasting. You are aware that you can upload an Excel file to the forum directly (Attachments, Add Files, Start Upload)

Noticed that the Results sheet was not in line with the information in the WIP sheet. Changed to WIP sheet as I guessed you wanted to show different Status for some of the PO numbers. 

The leading zeroes in the WIP table can be added using Text.PadStart([PO Number],10,"0"). I've done that in the attached file.

Avatar
Justin Redua
Member
Members
Level 0
Forum Posts: 22
Member Since:
February 18, 2022
sp_UserOfflineSmall Offline
3
June 21, 2022 - 4:25 pm
sp_Permalink sp_Print

Hi Riny,

I'm sorry about the difficulty in downloading the file.

for the point number 3 it's solved.

for number one, i do the step like on picture 1 - 5. but somehow on pic no 5, i can't click ok. did i made a wrong step ?

if i used Append Queries it will happen like picture no 6.

 

4.pngImage Enlarger

5.pngImage Enlarger
6.pngImage Enlarger

1.pngImage Enlarger

2.pngImage Enlarger
3.pngImage Enlarger

Kind Regards,

Justin R

sp_PlupAttachments Attachments
  • sp_PlupImage 1.png (83 KB)
  • sp_PlupImage 2.png (98 KB)
  • sp_PlupImage 3.png (157 KB)
  • sp_PlupImage 4.png (177 KB)
  • sp_PlupImage 5.png (119 KB)
  • sp_PlupImage 6.png (137 KB)
Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 439
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
4
June 21, 2022 - 6:04 pm
sp_Permalink sp_Print

Not sure what you are trying to achieve but to focus just on your question re Pic 5, when you do a merge you need to identify the key columns in each of the tables. In this case the key for merging is the PO Number, so select those columns and you can press OK.

Avatar
Justin Redua
Member
Members
Level 0
Forum Posts: 22
Member Since:
February 18, 2022
sp_UserOfflineSmall Offline
5
June 21, 2022 - 8:22 pm
sp_Permalink sp_Print

Hi Riny,

Okay it's worked and have another issue.

there is some case that makes a PO is divided into 2 and then add the character behind the PO.
how to combine 2 files using power query and get the quantity from each section ('WIP' sheet, C until K column) based on the smallest number of each section ?

Kind Regards,

Justin R

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 439
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
6
June 21, 2022 - 10:30 pm
sp_Permalink sp_Print

It's a bit of coding and works for this small example but I can't guarantee it will work on your real data set. Have a look at the attached file and see what happens.

sp_AnswersTopicAnswer
Answers Post
Avatar
Justin Redua
Member
Members
Level 0
Forum Posts: 22
Member Since:
February 18, 2022
sp_UserOfflineSmall Offline
7
July 13, 2022 - 4:53 pm
sp_Permalink sp_Print

Hi Riny,

I will try it. Thankyou

Kind Regards,

Justin R

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Tristram Sexton, Ben Hughes, Dario Serrati, Christopher Anderson
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
yashal minahil
Oluwadamilola Ogun
Yannik H
dectator mang
Francis Drouillard
Orlando Inocente
Jovitha Clemence
Maloxat Axmatovna
Ricardo Freitas
Marko Meglic
Forum Stats:
Groups: 3
Forums: 24
Topics: 6200
Posts: 27182

 

Member Stats:
Guest Posters: 49
Members: 31861
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.