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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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