question

William-7599 avatar image
0 Votes"
William-7599 asked Lz-3068 answered

Compare two data columns with different time stamp columns

I am trying to compare two data columns with different time stamp columns. The first columns look like this: 88085-image.png


The second columns look like this: 88096-image.png


And i want to compare them like this: 88142-image.png



I have thousands rows in my columns to compare, so an answer to this problem would really help me.

power-query-not-supported
image.png (2.3 KiB)
image.png (2.8 KiB)
image.png (3.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 avatar image
0 Votes"
Lz-3068 answered

@William-7599
I didn't change anything to your data, I took what I found in sheet Ark1, that's it.
In this updated version I copied/pasted the values from sheet Ark2 into Tabell1 and reformatted the 4 columns
Is this OK now?


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 avatar image
0 Votes"
Lz-3068 answered Lz-3068 converted comment to answer

Hi @William-7599

2 columns named "Time" means those columns are necessarily in different tables, what I assumed below:

 let
 // Tables for demo
     Table1 = Table.FromRows(
         {
                 {#time(0,0,0),"A"},{#time(2,0,0),"B"},{#time(3,0,0),"C"},
          {#time(5,0,0),"D"},{#time(6,0,0),"Z"}},
         type table [Time=time, Column1=text]
     ),
     Table2 = Table.FromRows(
         {
                 {#time(0,0,0),"E"},{#time(1,0,0),"F"},{#time(2,0,0),"G"},
         {#time(3,0,0),"H"},{#time(4,0,0),"I"},{#time(5,0,0),"J"}},
         type table [Time=time, Column2=text]
     ),
 //
     JoinedTables = Table.NestedJoin(
         Table1,"Time",
         Table2,"Time",
         "Tbl2", JoinKind.Inner // or JoinKind.LeftOuter depend. on the expected result
     ),
     Expanded = Table.ExpandTableColumn(JoinedTables, "Tbl2", {"Column2"}, {"Column3"})
 in
     Expanded
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for the reply, but the problem about this method is that it uses the numbers ABCDEFGHIJ in the formula, which does not work in my case because i have random number values (eg. 2.9, 3.1 ...). Is it possible to find a similar formula with just the time values instead, not the data values?

0 Votes 0 ·

@William-7599
What I suggested does exactly what you described with your initial pictures and I don't understand what you're now asking. So please upload and share (i.e. with OneDrive) a workbook with dummy (but representative) data and the expected result. Thanks


0 Votes 0 ·

Here is a sample with temperature measurements:

91260-image.png


And here is the expected results:

91392-image.png


0 Votes 0 ·
image.png (10.6 KiB)
image.png (6.0 KiB)
Lz-3068 avatar image
0 Votes"
Lz-3068 answered William-7599 edited
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

You have probably replaced comma with punctation, then it converts e.g 1.5 to 1st of may 2021. Can you present a new table, which only has comma for temperature values?

0 Votes 0 ·