Generate sequence Hours Between range

M

muttleychess

New member
Joined
Sep 25, 2017
Messages
9
Reaction score
0
Points
1
Excel Version(s)
2016
Hi

I would like to know how can I to generate a sequence hours between two range hours same date.

I have 4 columns

name,
Date
Hrini
Hrfim

Example
DONALD 01/08/2019 11:32 15:59:59


I Would like show with PQ
DONALD 01/08/2019 11
DONALD 01/08/2019 12
DONALD 01/08/2019 13
DONALD 01/08/2019 14
DONALD 01/08/2019 15
 

Attachments

  • case02.xlsx
    15.4 KB · Views: 15
Code: 
let Fonte = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
#"Tipo Alterado" = Table.TransformColumnTypes(Fonte,{{"DATE", type date}, {"hrini", type time}, {"hrfim", type time}}),
#"Added Custom" = Table.AddColumn(#"Tipo Alterado", "Custom", each {Time.Hour([hrini])..Time.Hour([hrfim])}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"hrini", "hrfim"})
in #"Removed Columns"
 
Last edited by a moderator:
Some of the PQ code appears not to be in English, so could you add a workbook so that the code is translated for anyone wanting this solution? Thanks.

Really nice solution! :)
 
I did. The file link is there
And it is all in English -- only irrelevant step names are in original language of question
 
Thanks for the file. Neat solution. :)

Here's the M Code as it would appear in an English version:

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"hrini", type time}, {"hrfim", type time}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {Time.Hour([hrini])..Time.Hour([hrfim])}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"hrini", "hrfim"})
in
    #"Removed Columns"
 
You must log in or register to reply here.
Back
Top