Forum Discussion
Matching different data sets based on time
Aditya Jadhav In PQ you can easily split the date and time components. First, make sure that the column is of the "Date/time" data type. Select the Date/Time column with the add column tab active. Select the Time icon, Time only. That will extract the time portion into a new column. Then do something similar with the transform column tab active. Select the Date/Time column and choose Date, Date only. That changes the column to hold only the date. Finally, select the time column (add column tab active) and select Time, Hour to create a new column with only the hour component.
PS: Wrote this answer from memory as I'm not near a computer that has Excel wit PQ on it. So perhaps, these instructions aren't 100% correct, but I trust you'll be able to figure it out.
Aditya Jadhav Not sure what you are trying to achieve, but I reworked the data with PowerQuery and created one dataset that might represent what you need. From that, I created one Pivot Table / Chart.
Note that the data set has quite a big spread of values. From very small to fairly large. Plotting such values in on graph might not convey the correct message as many values will simply not be big enough to see. Anyway, have a look at the attached file and see if it's useful. Using a secondary axis for on or more series might work though.
- Riny_van_Eekelen can you just tell me how did you get the day and hour thing in the pivot table will help me a lot at this moment. Rest i could figure and have tried the steps after reading them it's just that the pivot column (Hour) is missing in my pivot now rest is very good.
Aditya Jadhav In PQ you can easily split the date and time components. First, make sure that the column is of the "Date/time" data type. Select the Date/Time column with the add column tab active. Select the Time icon, Time only. That will extract the time portion into a new column. Then do something similar with the transform column tab active. Select the Date/Time column and choose Date, Date only. That changes the column to hold only the date. Finally, select the time column (add column tab active) and select Time, Hour to create a new column with only the hour component.
PS: Wrote this answer from memory as I'm not near a computer that has Excel wit PQ on it. So perhaps, these instructions aren't 100% correct, but I trust you'll be able to figure it out.
- Riny_van_Eekelen The sheet that you shared is very close to what i want now I need the process of the PowerQuery so then that can be used for the larger data set. Now what I'm trying to achieve is the relation between 3 parameters and how they affect each other. Have not used PowerQuery before hence asking you the process.
Regards,
AdityaAditya Jadhav This would be a good starting point to learn more about Power Query. Once you get used to it, you'll love it and will use it whenever you can.
- Dear Riny_van_Eekelen Have already started to look into this and it is very helpful once understood, this is a very powerful tool that helps us in many ways and is easy once understood for large datasets.
Regards,
Aditya
