Create table using DAX

PSdhillon — Wed, 03 Jul 2019 11:24:46 GMT

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

Re: Create table using DAX

PSdhillon — Thu, 04 Jul 2019 04:23:08 GMT

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

Re: Create table using DAX

PSdhillon — Thu, 04 Jul 2019 13:14:59 GMT

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"

topic Re: Create table using DAX in DAX Commands and Tips

Create table using DAX

Re: Create table using DAX

Re: Create table using DAX