Oct 25 2021 03:11 AM
Hi,
Have 3 different data sets whose time don't match and need that to compare with each other by plotting it on the same graph but that is very difficult in a very big data set. Have made 3 pivot tables but need them all to get on a common thing where they can be compared. For this would really be helpful if the way out could be suggested.
Attaching the excel sheet for your reference.
Oct 25 2021 03:56 AM
@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.
Oct 25 2021 04:12 AM
Oct 25 2021 04:17 AM
@Aditya 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.
Oct 25 2021 04:59 AM
Oct 25 2021 10:50 PM
Oct 25 2021 11:55 PM
Solution@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.
Oct 26 2021 01:54 AM
Oct 27 2021 05:20 AM
@Sergei Baklan @Riny_van_Eekelen
One more help needed with this now. Have tried working with the raw data pulling all the tables into a PQ editor but that does not work now. The tables that are put up in the pivot and grouped for a 10 min interval is what i need but the only thing is that the data needs to be copied manually then the power query thing works like a charm. Need help in this process where the data can be copied automatically to a new table based on the date time and the value i front of it. Also have commented the required things in comments in the pivot tab.
Attaching the file for ready reference.
Regards,
Aditya
Oct 27 2021 05:32 AM
@Aditya Jadhav I can see the Raw tables and the Pivot Table. You seem to connect to a file on your local drive. Not really sure what doesn't work in PQ or why and what you need further. Sorry!
Oct 25 2021 11:55 PM
Solution@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.