Power Query Add Step

[Colin970]

Hi,

I am unable to work out in power query how to add a step to calculate Column "Time-Copy.1" to have a column produce the "Required Result.

Hope somebody can help

Regards

Colin

Time-Copy.1                   Required Result

12:29 PM                                 0

null                                          0

1:02 PM                                   0

null                                          0

1:37 PM                             Top2-2

Top-2                                 Top2-2

2:12 PM                                  0

null                                         0

2:47 PM                                  0

null                                         0

3:23PM                             Top-2

Top-2                                Top-2

4:02 PM                            Top-2

Top-2                                Top-2

4:42 PM                            Top-2

null                                       0

[#2]

@Colin970 At a first glance it seems that you want the time stamp to show Top-2 if the row below it has Top-2 in it. But that logic fails for the 4:42 PM at the bottom as it should have 0, not Top-2.  So please clarify.

[#3]

@Riny_van_Eekelen 

Hi, you are correct the 4:42PM should be zero

Colin

[#4 · Accepted solution]

@Colin970 See attached. Created the query in PQ for the Mac. It has a slightly different set-up. That's why the initial steps look a bit different. Didn't bother to re-write the M-code.

[#5]

Hi

Thats great do i add this to my sheet

[#6]

@Colin970 Are you asking how you should add this to my sheet?

I assume you are somewhat familiar with PQ. Then you should be able to follow the applied steps in my file and create similar ones in your own file. Perhaps you can just copy most of them over directly. I can't tell without seeing your file.

[#7]

Great I will try that ,I do have a limited knowledge of PQ hope it works thanks again

Colin

[#8]

Hi

I have tried and tried even to the point of gettind a headache I an struggling to reproduce step 4 "Expand Added Index1"

[#9]

Hi

I have tried and tried even to the point of gettind a headache I an struggling to reproduce step 4 "Expand Added Index1"

[#10]

@Colin970 Can you share a sample file via OneDrive or similar? Just remove anything confidential, as long as the  structure/layout remains the same as your real file.

[#11]

Hi

See link thers is no sensative information. Excuse my attemps to consoliate, if its too hard or you do not have time I understand. Thanks again

https://1drv.ms/u/s!AuL2hHx5FRiPib1SSjtT7GQiVgGyNg?e=Ubt3Tz

[#12]

@Colin970 And now please explain what you've sent and where I should begin looking.

[#13]

@Riny_van_Eekelen Hi yes I was not sure what to send you so I zipped all information. If you look at the spread sheet TAB 'TOP 2' that's where you will find the Query that I have been trying to update.. Hope this helps Regards Colin

[#14]

@Colin970 Sorry, but this is simply too much to dive into. It would take half a day to figure out what you are doing and to make it work. Looked again what I had sent earlier and think it should be rather straight-forward to implement those few steps into your data set. 

All it does is take the first column and adds two index columns. One starting at zero the other starting at one. The Merge and Expand steps put the information from "row 2" on to "row 1" and so on. Then the Added column "translates" that information to either a 0 or the word "Top-2".

Perhaps it would help if you started from scratch. Now, you have three attempts to get files from a folder and a lot of, what seem to be unnecessary steps, that just clutter the whole lot. I'm working on a powerful PC but my Excel hung-up after trying to reset the connection to the source files included in the zip-file. So, I gave up.

[#15]

Hi

Thank you for comments they are correct, thank you for even taking the time the look the data.

Colin

[#16]

Should read "taking the time to look the data"

[#17]

Should read "taking the time to look at the data"