• 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 do I split out data into different columns when there's no apparent way to parse them out|Power Query|Excel Forum|My Online Training Hub

You are here: Home / How do I split out data into different columns when there's no apparent way to parse them out|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 do I split out data into differ…
sp_PrintTopic sp_TopicIcon
How do I split out data into different columns when there's no apparent way to parse them out
Avatar
Anne Walsh
Member
Members
Level 0
Forum Posts: 100
Member Since:
May 2, 2014
sp_UserOfflineSmall Offline
1
June 20, 2018 - 8:46 pm
sp_Permalink sp_Print

I've been experimenting with PQ on this attached QuickBooks file (dummy company). In the attached file (Column 1 and Column 1 - Copy) I want to have fill down of Air Conditioning, then Brakes, Electrical - your basic departments. Then in Column 1 - Copy I want to end up with Part numbers e.g. 120Y, K1212. Of course the Parts data is not consistently number. At this point I have tried: Duplicate Columns, Column from Examples, Add a suffix, add a prefix, Custom Columns plus the Fill Down/Fill up (that wouldn't work for some reason when I tried importing CSV from a folder)  My (so far, not so cunning) plan was to see if I could have a folder with these QuickBooks CSV files and then with a file that would do all this cleaning for me. What am I missing? Thanks. 

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4446
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
June 20, 2018 - 9:28 pm
sp_Permalink sp_Print

Hi Anne,

Got me stumped! There is no pattern that I can see that you can exploit. I think you need a lookup table that maps the departments to the part numbers. You can then merge the two tables and bring in the department on the same row as the invoice.

Mynda

Avatar
Anne Walsh
Member
Members
Level 0
Forum Posts: 100
Member Since:
May 2, 2014
sp_UserOfflineSmall Offline
3
June 21, 2018 - 5:25 pm
sp_Permalink sp_Print

Thank you for getting back to me. So it wasn't just me! Let me go and try that then. Any thoughts on why the Fill Down/Fill up wouldn't work in the CSV file (I hadn't removed the file name?) . Thanks again! 

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
4
June 21, 2018 - 6:37 pm
sp_Permalink sp_Print sp_EditHistory

Hi Anne

Although I don't have PQ, I am interested to know what is your expected result from your attachment.

It is not too clear for me from your description.

If you want to fill in the part number in column A beside the Invoice maybe you can try using the F5-Goto-Special-Blanks trick.

Cheers

Sunny

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
5
June 22, 2018 - 4:37 am
sp_Permalink sp_Print

Hi Anne,

Fill Up/Down will not work until you have null's instead of blanks.

Before Fill Up/Down, Replace "" with null in the columns you want. (in the first field of Replace function do not type anything, just type null in the Replace with field)

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
6
June 22, 2018 - 11:55 am
sp_Permalink sp_Print

Hi Anne

Is this what you are trying to achieve?

Sunny

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
7
June 22, 2018 - 1:41 pm
sp_Permalink sp_Print

Hi Anne,

First, the Total rows should be removed, you don't need them, as you will most probably create your own reports.

After you do this, a pattern will show up: if in Type column is "Invoice" and the 2 cells above it are empty (null), then the category can be taken from Column1, 2 rows above Invoice.

To accomplish this, we have to use a method that can read the row number, obviously based on an Index column.

After you add an Index column, I used this formula to add a new column:

= Table.AddColumn(#"Added Index", "Custom", each if [Type]="Invoice" then (if (#"Added Index"[Type]{[Index]-1}=null and #"Added Index"[Type]{[Index]-2}=null) then #"Added Index"[Column1]{[Index]-2} else null) else null)

Fill this new column down, remove any rows with null in Type column and that's it.

Sample file attached.

Avatar
Anne Walsh
Member
Members
Level 0
Forum Posts: 100
Member Since:
May 2, 2014
sp_UserOfflineSmall Offline
8
June 26, 2018 - 5:15 pm
sp_Permalink sp_Print

Thank you for all your help here - much appreciated. I did get it all working...What I did in the end had a certain amount of brute force about it (ahem!). I cleaned out the totals. Duplicated the column that had the department and then using a combination of Find and Replace to clean out individual entries - yes, I know, not great but it got the job done. 🙂  @Catalin Bombea - as you rightly said, I found that I had to clean out stuff to get it to Null and then the Fill Down worked for the invoices. 

Most intrigued by your formula @Catalin and will have a closer look. THANK YOU ALL SO MUCH 

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Shanna Henseler, Lawrence Smith, Nada Perovic
Guest(s) 11
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:
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Ray-Yu Yang
George Shihadeh
Naomi Rumble
Uwe von Gostomski
Jonathan Jones
drsven
Forum Stats:
Groups: 3
Forums: 24
Topics: 6212
Posts: 27236

 

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