+ Reply to Thread
Results 1 to 4 of 4

Power Query - Expand Table Regardless of Text Case

  1. #1
    Forum Contributor
    Join Date
    09-04-2013
    Location
    USA
    MS-Off Ver
    Excel 365
    Posts
    263

    Power Query - Expand Table Regardless of Text Case

    I have created a Custom Function in Power Query to return online data for multiple years in Excel.

    Unfortunately, in different years they changed the text case of some (but not all) of the column headers, thereby giving be a "null" value when there should be data. Any idea how to change. See sample below. The "Expanded Column1" step is the problem, as I can see data in the "converted to table" section. I have tried using Comparer.OrdinalIgnoreCase operator, but it did not work. Any ideas? Thanks.

    Please Login or Register  to view this content.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Power Query - Expand Table Regardless of Text Case

    Have you considered making all data in that field either All CAPS or all lower case before doing any conversions. Best I can offer without seeing actual data
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Contributor
    Join Date
    09-04-2013
    Location
    USA
    MS-Off Ver
    Excel 365
    Posts
    263

    Re: Power Query - Expand Table Regardless of Text Case

    Thanks. Was able to solve by doing exactly that...Code below..

    Please Login or Register  to view this content.

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Power Query - Expand Table Regardless of Text Case

    Good to hear. Thanks for the Rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 11-22-2021, 05:22 PM
  2. Excel and Power Query table, hyperlinks set as text
    By bluesky61 in forum Excel General
    Replies: 2
    Last Post: 03-10-2021, 06:42 AM
  3. Unable To Expand XML Table In Excel Power Query Editor
    By alteredstate in forum Excel General
    Replies: 3
    Last Post: 09-12-2020, 06:20 AM
  4. Replies: 3
    Last Post: 04-25-2020, 09:46 AM
  5. POWER Query and Power Pivot Table !!
    By haitham.shop in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-06-2018, 04:09 PM
  6. Power Query and Power Pivot Table
    By haitham.shop in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-06-2018, 06:17 AM
  7. Power Query and Power Pivot Table !!
    By haitham.shop in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-06-2018, 06:15 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1