none
Power Query-OR, AND and Wildcard Characters RRS feed

  • Question

  • Hi All,

    I am new to power query and ever since the day I am using it, I have just loved it and also blown away with its potential. I wanna take this Opportunity to thank all the folks at Microsoft.

    Now to my question... Can we use OR and AND operators or wildcard characters in power query.

    Reason: I have a task where I am pulling certain data from a website. Now there is one column where the Column Name changes based on the time zone. ex: Time (PST), Time (PDT) etc. Now I need to pull this column irrespective of its extension(PST or PDT etc) and load it as one column. How do I do it. Request all your help please....

    Thanks David


    Sunday, October 11, 2015 12:37 PM

Answers

  • Hi Ehren,

    I was able to find the solution through multiple conditions. For my solution I have used functions like IF, Expand.Columns etc...

    Thanks,

    David


    DavidNischeal

    Friday, October 23, 2015 8:46 AM

All replies

  • Can you paste here an sample of the data and the desired output? We can show you how this can be done.

    Thank you,

    Gil

    Monday, October 12, 2015 10:04 AM
  • Hi Gil, 

    Thank you for your response.

    I actually need weather data from the link below, for table titled Hourly Weather History & Observations. Now for few dates the column name is Time (PDT) and for few dates the column name is Time (PST). I need both these data under one column as Time. As of now I am able to pull the data where column name is Time (PST) as the query has hard coded the column name and where the column name is different it obviously is showing Null. I am trying to pull data for multiple dates for my report. Need help on that. Thanks David

    link: http://www.wunderground.com/history/airport/KYKM/2015/9/3/DailyHistory.html?req_city=Yakima&req_state=WA&req_statename=Washington&reqdb.zip=98903&reqdb.magic=1&reqdb.wmo=99999&MR=1

    Monday, October 12, 2015 10:42 AM
  • Hi David. Have you tried using any of the filter options exposed via the UI?

    https://support.office.com/en-us/article/Filter-a-table-Power-Query-b5610630-f5bf-4ba4-9217-a628f9b89353

    Ehren

    Tuesday, October 20, 2015 5:06 PM
    Owner
  • Hi Ehren,

    I was able to find the solution through multiple conditions. For my solution I have used functions like IF, Expand.Columns etc...

    Thanks,

    David


    DavidNischeal

    Friday, October 23, 2015 8:46 AM
  • Don't sure if I catch you right, I suppose you got tables for sevral days and then trying to combine it in one

    then you can rename columns and combine tables as here:

    let
    // Day1 Source
    
        Tab1 = Excel.CurrentWorkbook(){[Name="Table_33"]}[Content],
        Time1= Table.RenameColumns(Tab1,{{if Table.ColumnNames(Tab1){0} = "Time (PST)" then "Time (PST)" else "Time (PDT)","Time"}}),
    
    // Day 2 Source
        Tab2 = Excel.CurrentWorkbook(){[Name="Table_3"]}[Content],
        Time2= Table.RenameColumns(Tab2,{{if Table.ColumnNames(Tab2){0} = "Time (PST)" then "Time (PST)" else "Time (PDT)","Time"}}),
    
    // Combine
        Comb = Table.Combine({Time2,Time1}),
        Typ = Table.TransformColumnTypes(Comb,{{"Time", type time}})
    in
        Typ

    or just look at renaming code for ANY first column name:

    Time1= Table.RenameColumns(Source,{{Table.ColumnNames(Source){0},"Time"}})



    • Edited by Maxim ZelenskyMVP Friday, October 23, 2015 9:47 AM edited last code to ANY first column name
    • Proposed as answer by Maxim ZelenskyMVP Friday, October 23, 2015 9:47 AM
    Friday, October 23, 2015 9:33 AM