Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join 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.

Reply
PSdhillon
Frequent Visitor

Create table using DAX

Hi everyone, 

 

I am new to DAX, need small help to create table with two static column with value 1D & 1 in Period & Sort column. Whereas, The date will have hourly rows for today'S DATE except 00 hours. Here is the screenshot, what is required

Screenshot 2019-07-03 at 4.47.41 PM.png

Please help/guide me to get this. TIA

1 ACCEPTED SOLUTION

Here is how i solved the problem, Thought to put it here, so that someone may use code specially beginers like me.

let
    Source = List.Dates(DateTime.Date(DateTime.LocalNow()), 1, #duration(-1, 0, 0, 0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Time", each List.Times(#time(1, 0, 0), 23, #duration(0, 1, 0, 0))),
    #"Expanded Time" = Table.ExpandListColumn(#"Added Custom", "Time"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Time",{{"Time", type time}, {"Date", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "DateTime", each [Date]&[Time]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"DateTime", type datetime}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Date", "Date0"}, {"DateTime", "Date"}}),
    #"Inserted Literal" = Table.AddColumn(#"Renamed Columns1", "Period", each "1D", type text),
    #"Inserted First Characters" = Table.AddColumn(#"Inserted Literal", "First Characters", each Text.Start([Period], 1), type text),
    #"Renamed Columns2" = Table.RenameColumns(#"Inserted First Characters",{{"First Characters", "Sort"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns2",{"Time", "Date0"})
in
    #"Removed Columns"

View solution in original post

2 REPLIES 2
PSdhillon
Frequent Visitor

I have found a way to generate the time column using some altenative, its just the column Period is making problems now as i am not able to addcolumn with text value in Dax. Please guide me to do so, in case not complete solution

Here is how i solved the problem, Thought to put it here, so that someone may use code specially beginers like me.

let
    Source = List.Dates(DateTime.Date(DateTime.LocalNow()), 1, #duration(-1, 0, 0, 0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Time", each List.Times(#time(1, 0, 0), 23, #duration(0, 1, 0, 0))),
    #"Expanded Time" = Table.ExpandListColumn(#"Added Custom", "Time"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Time",{{"Time", type time}, {"Date", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "DateTime", each [Date]&[Time]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"DateTime", type datetime}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Date", "Date0"}, {"DateTime", "Date"}}),
    #"Inserted Literal" = Table.AddColumn(#"Renamed Columns1", "Period", each "1D", type text),
    #"Inserted First Characters" = Table.AddColumn(#"Inserted Literal", "First Characters", each Text.Start([Period], 1), type text),
    #"Renamed Columns2" = Table.RenameColumns(#"Inserted First Characters",{{"First Characters", "Sort"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns2",{"Time", "Date0"})
in
    #"Removed Columns"

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.