Concatenate two fields into 1 and convert a string to date

[Anonymous]

Hello, I would like some help to create 1 column DAX field where I can combine the Day and Date columns so that the timestamp is only coming over as dd/mm/yyyy and Day Name
Doe anyone have any ideas?

Thanks
Patrick
Developer

------------------------------
Patrick Forbes
Developer

[v-kkf-msft]

Hi @Anonymous ,

Has your problem been solved? If it is solved, please mark a reply which is helpful to you.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[v-kkf-msft]

Hi @Anonymous ,

How would you like to combine the Day and Date columns? Is it like this?

If so, please open the blank query in Power Query Editor and paste the following code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRci7NLc1JLMksS1UoyVdwSSxJVXBLTSotKk0sqlQIyS9JzAEqAiIDpVid4aHByMDISNdI10zBwMrAACLhEe4S5A5iDnr5WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Day = _t, Category = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Day", type text}, {"Category", type text}, {"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date", "Day"}, {{"AllRows", each _, type table [Date=nullable datetime, Day=nullable text, Category=nullable text, Value=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each if [Day]<>"" then Table.AddIndexColumn([AllRows], "Index_Day", 1, 1, Int64.Type) else if [Date]<>"" then Table.AddIndexColumn([AllRows], "Index_Date", 1, 1, Int64.Type) else null),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Category", "Value", "Index_Day", "Index_Date"}, {"Category", "Value", "Index_Day", "Index_Date"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"AllRows"})
in
    #"Removed Columns"

let
    Table1 = Table.SelectRows(Table, each [Index_Day] <> null),
    Table2 = Table.SelectRows(Table, each [Index_Date] <> null),
    MergeTables = Table.NestedJoin(Table1, {"Index_Day"}, Table2, {"Index_Date"}, "MergeTable", JoinKind.FullOuter),
    #"Expanded MergeTable" = Table.ExpandTableColumn(MergeTables, "MergeTable", {"Date", "Day", "Category", "Value"}, {"MergeTable.Date", "MergeTable.Day", "MergeTable.Category", "MergeTable.Value"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded MergeTable", {{"Date", type text}, {"MergeTable.Date", type text}}, "en-US"),{"Date", "MergeTable.Date"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Date"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Day", "MergeTable.Day"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Day"),
    #"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"Category", "MergeTable.Category"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Category"),
    #"Merged Columns3" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns2", {{"Value", type text}, {"MergeTable.Value", type text}}, "en-US"),{"Value", "MergeTable.Value"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Columns3",{"Index_Day", "Index_Date"})
in
    #"Removed Columns"

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tamerj1]

Hi @Anonymous 
Not sure if the blanks in the sample data you've presented have something to do with you enquiry, but in general you use:
For a measure 

Timestamp =
CONCATENATEX ( Table, Table[Date] & " " & Table[Day] )

For a calculated column

Timestamp =
Table, Table[Date] & " " & Table[Day]