Power Query - Returning DateTime type rather than Date

[Comfy]

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

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

(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
	A	B	C
1	Journal Column Name	Column Value	Format
2	AccountNumber	Elavon M	Text
3	TransactionDate	29/07/2018	Date
4	TransactionReference	Invoice Number Here	Text
5	NominalAnalysisNominalAnalysisNarrative/1	Jul 18 charges	Text
6	NominalAnalysisNominalAnalysisNarrative/2	Jul 18 charges	Text
7	UserNumber	9	Number
Journal Data

Using this test query:

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:

And the value returned to Excel is null:

Book1
	A	B	C
1	Column1	AccountNumber	TransactionDate
2	1	Elavon 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?

 

[Comfy]

Now X-Posted on: https://powerpivotforum.com.au/viewtopic.php?f=5&t=1175

 

[Comfy]

I had to update the AddNColumns function to the following to get it working properly

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