Forum Discussion
Solved
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...
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"