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.
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
Please help/guide me to get this. TIA
Solved! Go to 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"
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"
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 |
---|---|
15 | |
13 | |
11 | |
9 | |
8 |