none
An all-purpose parameter query that adapts to Excel's data types? RRS feed

  • Question

  • This parameter query...

    (DateCell as text) as date =>
    let
        Source = Excel.CurrentWorkbook(){[Name=DateCell]}[Content],
        //Source = Excel.CurrentWorkbook(){[Name="MyDate"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
        DateValue = #"Changed Type"{0}[Column1]
    in
        DateValue
    ...is written to accept the name of any date-formatted cell and return its value. Is there a way to set up something like a Case statement that would return the contents of any named range while handling data types correctly? 

    Or, would it be best to set up parameter queries like pGetDate, pGetText, pGetNum, etc.?


    Founder, ExcelUser.com

    Sunday, June 10, 2018 4:06 PM

Answers

  • It depends on how the date is formatted. Since you originally used DateCell as text, I assumed that you had the cell formatted as either '<date>, <date> with number format text, or =TEXT(<date>, …). In all of these cases, Value.FromText will return a date value. On the other hand, if the date in the cell is a formatted serial number, a datetime value is returned. To handle this possibility, you'd need to add an additional step to the query:

    let
        Source = Excel.CurrentWorkbook(){[Name = "ValueCell"]}[Content],
        ParameterValue = Value.FromText(Table.FirstValue(Source)),
        Result = if Value.Type(ParameterValue) = type datetime then DateTime.Date(ParameterValue) else ParameterValue   
    in
        Result



    Sunday, June 10, 2018 10:45 PM

All replies

  • Hi Charlie,

    Table.TransformColumnTypes(Source,{{"Column1", type date}}) will convert text, number etc.. to type date. So i'm not sure why a case statement is needed. (I'm not sure I understand). 

    Another way of doing this (and it works for not just text) is:

    (DateCell as any) as date =>
    let
        Source = Excel.CurrentWorkbook(){[Name=DateCell]}[Content],
        FirstValue = Table.FirstValue(Source),
        dateFirstValue = Date.From(FirstValue)
    in
        dateFirstValue

    Sunday, June 10, 2018 5:45 PM
  • Sorry I wasn't clear. The sample handles only dates. I was wondering whether it would be possible to create a general-purpose routine that would automatically switch among dates, text, and numbers automatically. That would save having to maintain a separate parameter query for each data type.


    Founder, ExcelUser.com

    Sunday, June 10, 2018 7:52 PM
  • You could use this function instead:

    (Cell as text, Type as type) =>
    let
        Source = Excel.CurrentWorkbook(){[Name=Cell]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Type}}),
        Value = Table.FirstValue(#"Changed Type")
    in
        Value

    If this function is called Func then 

    Func("SomeNamedRange", type date) will return a date value (or an error, if it cannot convert)

    Func("SomeNamedRange", type text) will return a text value (or an error, if it cannot convert)

    etc...


    Sunday, June 10, 2018 9:26 PM
  • Hi Charley,

    The first thing that you would need to do is remove all references to a specific type in your names. So your Excel cell should be named ValueCell, instead of DateCell.

    Also, how would you get the parameter into your function query without creating another query to use the parameter, i.e. where does the actual value for DateCell come from? Unless I'm mistaken, you just need to use the Value.FromText function to convert the value from the cell to an appropriate value (date, number, text).

    let
        Source = Excel.CurrentWorkbook(){[Name = "ValueCell"]}[Content],
        ParameterValue = Value.FromText(Table.FirstValue(Source))   
    in
        ParameterValue
    Note: The name Value.FromText is somewhat misleading. For example, Value.FromText(2) returns 2, even though 2 is not a text value.

    Sunday, June 10, 2018 9:53 PM
  • Colin,

    Interesting. I didn't know that Value.FromText worked like that. I'll give it a try.

    Thanks!

    Charley


    Founder, ExcelUser.com

    Sunday, June 10, 2018 9:57 PM
  • Unfortunately, Value.FromText for a date returns the date-time, not the date. And that causes Expression.Error: We cannot apply operator < to types DateTime and Date. 

    However, we CAN apply the < operator to Date types. So the error message is incorrect. But whatever the message should be, the test still fails.

    Ah, well. It was a worthwhile thing to try.

    Charley


    Founder, ExcelUser.com

    Sunday, June 10, 2018 10:22 PM
  • It depends on how the date is formatted. Since you originally used DateCell as text, I assumed that you had the cell formatted as either '<date>, <date> with number format text, or =TEXT(<date>, …). In all of these cases, Value.FromText will return a date value. On the other hand, if the date in the cell is a formatted serial number, a datetime value is returned. To handle this possibility, you'd need to add an additional step to the query:

    let
        Source = Excel.CurrentWorkbook(){[Name = "ValueCell"]}[Content],
        ParameterValue = Value.FromText(Table.FirstValue(Source)),
        Result = if Value.Type(ParameterValue) = type datetime then DateTime.Date(ParameterValue) else ParameterValue   
    in
        Result



    Sunday, June 10, 2018 10:45 PM
  • Unfortunately, Value.FromText for a date returns the date-time, not the date. And that causes Expression.Error: We cannot apply operator < to types DateTime and Date. 

    However, we CAN apply the < operator to Date types. So the error message is incorrect.

    The message means that you can't compare a datetime value on the left of the operator with a date value on the right of the operator (or vice versa).


    Sunday, June 10, 2018 10:58 PM
  • Great! Thanks!


    Founder, ExcelUser.com

    Sunday, June 10, 2018 11:05 PM
  • Ahhhh!

    Founder, ExcelUser.com

    Sunday, June 10, 2018 11:05 PM