Forum Discussion

Oddie1209's avatar
Oddie1209
Frequent Visitor
4 years ago
Solved

create a microtable using date/hour between 2 date/hour from another table

Hi everyboy.

Please, i need some help in a problem.

I have a 2 tables.

The name of the first is Increment:

date/hour

Increments

18/03/2022 17:21:55

1

18/03/2022 17:28:47

2

18/03/2022 17:35:39

3

18/03/2022 17:49:50

4

18/03/2022 17:57:31

5

18/03/2022 18:10:11

6

18/03/2022 18:19:03

7

 

And the have a two columns (date/hour) and the second is the increments.

The second table is named movement.

Volume

Start

End

6930

18/03/2022 16:05

18/03/2022 17:30

5900

18/03/2022 17:31

18/03/2022 17:49

8290

18/03/2022 17:50

18/03/2022 18:12

4800

18/03/2022 18:13

18/03/2022 18:21

 

I want create a microtable in a movement table in a way to aggregate the lines of a increment table in the lines of a movement table.

Like this:

Volume

Start

End

Increment

6930

18/03/2022 16:05

18/03/2022 17:22

list

5900

18/03/2022 17:23

18/03/2022 17:49

list

8290

18/03/2022 17:50

18/03/2022 18:12

list

4800

18/03/2022 18:13

18/03/2022 18:21

list

 

...

Volume

Start

End

date/hour

Increments

6930

18/03/2022 16:05

18/03/2022 17:22

18/03/2022 17:21:55

1

6930

18/03/2022 16:05

18/03/2022 17:22

18/03/2022 17:28:47

2

 

This is a very important for my work and i don’t know how i can make this.

I do this only in a excel, so a want make this only in a power query.

Please, somebody can help me on thi?
Thanks a lot for all!

Need Help
Show and Tell
Tips and Tricks
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    Hi Oddie1209 ,

     

    Paste this code into a new blank query in PQ:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZczBDQAhCETRVjacTYRBXbAVYv9taDzK9c/LRNBwZSokVlkrGPhkTO5P+udRqwR155efTVJqfrnBM+9vsim4vFl6P5umBKG1Ng==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Volume = _t, Start = _t, End = _t]),

    chgTypes = Table.TransformColumnTypes(Source,{{"Volume", Int64.Type}, {"Start", type datetime}, {"End", type datetime}}),
    bufferIncrement = Table.Buffer(increment),
    addNewIncrement = Table.AddColumn(chgTypes, "newIncrement", (OT) => Table.SelectRows(bufferIncrements, each OT[Start] <= [dateTime] and OT[End] >= [dateTime])[increments], type number)
in
    addNewIncrement

     

    I get the following output:

     

    Pete

2 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    Hi Oddie1209 ,

     

    Paste this code into a new blank query in PQ:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZczBDQAhCETRVjacTYRBXbAVYv9taDzK9c/LRNBwZSokVlkrGPhkTO5P+udRqwR155efTVJqfrnBM+9vsim4vFl6P5umBKG1Ng==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Volume = _t, Start = _t, End = _t]),

    chgTypes = Table.TransformColumnTypes(Source,{{"Volume", Int64.Type}, {"Start", type datetime}, {"End", type datetime}}),
    bufferIncrement = Table.Buffer(increment),
    addNewIncrement = Table.AddColumn(chgTypes, "newIncrement", (OT) => Table.SelectRows(bufferIncrements, each OT[Start] <= [dateTime] and OT[End] >= [dateTime])[increments], type number)
in
    addNewIncrement

     

    I get the following output:

     

    Pete

  • AilleryO's avatar
    AilleryO
    Memorable Member
    4 years ago

    Hi,

     

    Sorry but I'm not sure to understand your goal.

    Is the 1st line of table one should be "merged" with the first one of table two, and then 2nd line with 2nd line, and so on ?

    If this is the case, you can add an index column to table 2, and then merge using that index.

    Is this what you need ?