Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
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
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.
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]
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
9 |