Page 2 of 2 FirstFirst 1 2
Results 11 to 16 of 16

Thread: Master Power Query with our book - M is for Data Monkey

  1. #11
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,373
    Articles
    45
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider


    Register for a FREE account, and/
    or Log in to avoid these ads!

    Quote Originally Posted by oldhasbeen View Post
    A couple of thoughts on this:

    • Do I need to install special software on my laptop, e.g SQL server?
    No, that shouldn't be necessary at all.

    Any chance you're behind a corporate firewall? That's usually the number one blocker of these things. Try it from a home network?
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  2. #12
    Seeker oldhasbeen's Avatar
    Join Date
    Jul 2019
    Posts
    6
    Articles
    0
    Excel Version
    2019
    Thanks Ken.

    I'm on a home network, with a McAfee security package providing firewall protection

  3. #13
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,401
    Articles
    0
    Excel Version
    Office 365 Subscription
    I am on a home network with McAfee and don't have these issues - I would think that something else is at play here. Do you have extra firewall settings on your router?
    Ali
    Enthusiastic self-taught user of MS Excel!

  4. #14
    Seeker oldhasbeen's Avatar
    Join Date
    Jul 2019
    Posts
    6
    Articles
    0
    Excel Version
    2019
    Hi Ali,

    I'm going to try this from a different computer today/tomorrow and will see what happens. Will let you know

  5. #15
    Seeker R1C1's Avatar
    Join Date
    May 2020
    Posts
    9
    Articles
    0
    Excel Version
    Microsoft Excel 2016 Pro Plus
    Mr. Ken,

    When I had seen Power Query in Excel 2013, I thought this is the end for all my problems related to Date formats. Power Query is definitely solves most of our data transforming problems. One of the most classic problems is date formats. But strangely there is no straight way still after 7 years for changing dates from DD/MM/YYYY to MM/DD/YYYY or any other formats like YYYY/DD/MM, YYYYMM/DD etc. Am I right or am I ignorant of some option available already?

    Srinivas
    Last edited by R1C1; 2020-05-04 at 03:17 AM. Reason: typos

  6. #16
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,373
    Articles
    45
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    One of the most classic problems is date formats. But strangely there is no straight way still after 7 years for changing dates from DD/MM/YYYY to MM/DD/YYYY or any other formats like YYYY/DD/MM, YYYYMM/DD etc.
    No, not exactly. The thing is that Power Query doesn't deal with formats, it deals with data types, and Excel deals with formats. What I mean is that Power Query's job is to convert your data from it's provided format (DD/MM/YY, YY/MM/DD, MM/DD/YY, etc..) and convert it to the correct date serial number. That gets placed in the Excel cell, then you format it with whatever format you want to display it in. To do this, you need to use the Using Locale setting on your Change Type options.

    The key to this is understanding that this function is typically only applied when you are importing data from a flat file (txt, csv, prn, etc). Once the data is in an Excel cell, the date serial number has already been created, so it's too late to change it.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Page 2 of 2 FirstFirst 1 2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •