Power Query - Returning DateTime type rather than Date

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,386
Hi Boardies,

I have written the following functions so that I can add columns to a table recursively using information in a second table:

Function: AddNColumns
Code:
let


    Source = (TableToAddTo as table, ColumnsToAdd as table, optional RowNumber as number) =>
   
    let
        ActualRowNumber = if (RowNumber=null) then 0 else RowNumber,
        AddColumn = Table.AddColumn(TableToAddTo, ColumnsToAdd{ActualRowNumber}[Journal Column Name], each ColumnsToAdd{ActualRowNumber}[Column Value], GetType(Text.Lower(ColumnsToAdd{ActualRowNumber}[Format]))),
        NextRowNumber = ActualRowNumber + 1,
        OutputTable = if NextRowNumber > (Table.RowCount(ColumnsToAdd) - 1)
                    then
                        AddColumn
                    else
                        AddNColumns(AddColumn, ColumnsToAdd, NextRowNumber)
    in
        OutputTable
in
    Source

Function: GetType
Code:
(typeText as text) as type=>


let


// typeText = "text",
     values = {


    {"null", type null},
    {"logical", type logical},
    {"number", type number},
    {"time", type time},
    {"date", type date},
    {"datetime", type datetime},
    {"datetimezone", type datetimezone},
    {"duration", type duration},
    {"text", type text}
    },
    ReturnMatches = List.Select(values, each _{0}=typeText),
   
    Result = if List.IsEmpty(ReturnMatches)
        then
            type null
        else
            List.First(ReturnMatches){1}


in
     Result

Table: dFixedValues
Book1
ABC
1Journal Column NameColumn ValueFormat
2AccountNumberElavon MText
3TransactionDate29/07/2018Date
4TransactionReferenceInvoice Number HereText
5NominalAnalysisNominalAnalysisNarrative/1Jul 18 chargesText
6NominalAnalysisNominalAnalysisNarrative/2Jul 18 chargesText
7UserNumber9Number
Journal Data


Using this test query:

Code:
let
    Source = #table({"Column1"},{{1}}),
    Output = AddNColumns(Source, dFixedValues)
in
    Output

The returned data type in the Power Query Editor is Date but the actual value is DateTime:

VNjdu0H.jpg


And the value returned to Excel is null:

Book1
ABC
1Column1AccountNumberTransactionDate
21Elavon M
Sheet1


Also the Query screen shows 1 error.

This is all resolved if I set the type in table dFixedValues to DateTime.

Is there a perfectly good explanation for this that I've missed?
 
Last edited by a moderator:

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
I had to update the AddNColumns function to the following to get it working properly

Code:
let




    Source = (TableToAddTo as table, ColumnsToAdd as table, optional RowNumber as number) =>
    
    let
        ActualRowNumber = if (RowNumber=null) then 0 else RowNumber,
        AddColumn = if Text.Lower(ColumnsToAdd{ActualRowNumber}[Format]) = "date"
                    then    
                        Table.AddColumn(TableToAddTo, ColumnsToAdd{ActualRowNumber}[Journal Column Name], each DateTime.Date(ColumnsToAdd{ActualRowNumber}[Column Value]), GetType(Text.Lower(ColumnsToAdd{ActualRowNumber}[Format])))
                    else
                        Table.AddColumn(TableToAddTo, ColumnsToAdd{ActualRowNumber}[Journal Column Name], each ColumnsToAdd{ActualRowNumber}[Column Value], GetType(Text.Lower(ColumnsToAdd{ActualRowNumber}[Format]))),
        NextRowNumber = ActualRowNumber + 1,
        OutputTable = if NextRowNumber > (Table.RowCount(ColumnsToAdd) - 1)
                    then
                        AddColumn
                    else
                        AddNColumns(AddColumn, ColumnsToAdd, NextRowNumber)
    in
        OutputTable
in
    Source
 
Upvote 0
Solution

Forum statistics

Threads
1,212,927
Messages
6,110,728
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top