Forum Discussion

Praneeth19's avatar
Praneeth19
Frequent Visitor
2 years ago

create a table in power query

trying to create a table in query similar to 

the condition for this table is last 24 hours data but need it through power query not through dax as need to merge the data with different table

the requirement is a bit urgent if any one knows solution please reply i tried this in power query was getting an error

let
MaxStartTime = List.Max(Sheet1[Start_Time]),
StartTime = DateTimeZone.ToLocal(DateTime.From(DateTime.Date(MaxStartTime)) - #duration(23, 0, 0)),
EndTime = DateTimeZone.ToLocal(DateTime.From(DateTime.Date(MaxStartTime)) + #duration(23, 0, 0)),
GenerateSeries = List.Generate(
() => StartTime,
each _ <= EndTime,
each _ + #duration(0, 1, 0)
),
AddEndColumn = Table.FromList(GenerateSeries, Splitter.SplitByNothing(), {"Start Time"}),
AddEndColumnWithType = Table.AddColumn(AddEndColumn, "End Time", each [Start Time] + #duration(0, 1, 0))
in
AddEndColumnWithType

the errer was 
Expression.Error: 3 arguments were passed to a function which expects 4.
Details:
Pattern=
Arguments=[List]

Need Help

2 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    Praneeth19 

    Please try this code, I modified the Generate func as well:

    let
    MaxStartTime =  List.Max(Sheet1[Start_Time]),
    StartTime = DateTimeZone.ToLocal(DateTime.From(DateTime.Date(MaxStartTime)) - #duration(23, 0, 0, 0)),
    EndTime = DateTimeZone.ToLocal(DateTime.From(DateTime.Date(MaxStartTime)) + #duration(23, 0, 0, 0)),
    GenerateSeries = List.Generate(
        () => StartTime, 
        each _ <= EndTime,
        each _ + #duration(0, 1, 0 , 0),
        each _  
    ),
    AddEndColumn = Table.FromList(GenerateSeries, Splitter.SplitByNothing(), {"Start Time"}),
    AddEndColumnWithType = Table.AddColumn(AddEndColumn, "End Time", each [Start Time] + #duration(0, 1, 0, 0))
in
    AddEndColumnWithType