Forum Discussion
help with power query
thank you for the kind reply.
https://www.dropbox.com/s/oiodhrh5255bt8a/sampledatafor2016.xlsx?dl=0
but my data is not just for one day, i am sorry i was not clear with the question, i am very new to power bi and power queries. i have added a sample link for the file, it goes for 2016, july to end of year. and when i try to do this way , i am getting an error. list.dates gives list only for a month, actually i have data from july, 2016 to ,2017,2018, 2019 years . since power bi is not allowing me to have 24 hours rows for all these years in same table, i split them in multiple tables yearly.
could you check the sample data and suggest me some solution.
Hi Anonymous ,
One sample for your reference. Please check the following steps as below.
1. Create a table in power query like this.
let
Source = List.Dates(#date(2016,7,1), 3, #duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each {0..23}),
#"Expanded {0}" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Added Custom1" = Table.AddColumn(#"Expanded {0}", "DateTime", each DateTime.From([Date])+#duration(0,[Custom],0,0))
in
#"Added Custom1"
2. Close and apply, then we can create a calculated table based on it.
Table 2 = var a =ADDCOLUMNS(CROSSJOIN(Query1,'Table'),"st",'Table'[start time],"ed",'Table'[end time]) var b =FILTER(a,[DateTime]>=[st] && [DateTime]<=[ed]) return b
thank you for the kind reply.
https://www.dropbox.com/s/oiodhrh5255bt8a/sampledatafor2016.xlsx?dl=0
but my data is not just for one day, i am sorry i was not clear with the question, i am very new to power bi and power queries. i have added a sample link for the file, it goes for 2016, july to end of year. and when i try to do this way , i am getting an error. list.dates gives list only for a month, actually i have data from july, 2016 to ,2017,2018, 2019 years . since power bi is not allowing me to have 24 hours rows for all these years in same table, i split them in multiple tables yearly.
could you check the sample data and suggest me some solution.