Forum Discussion
Matrix change starting row
If you only have 24 values - 7am, 8am, 9am, etc for all 24 periods, you could create a custom column in Power Query to do this:
if [Column1] = "7am" then 1 else if [Column1] = "8am" then 2 else if [Column1] = "9am" then 3 else nullThat is 3. You just need to do the other 21. There must be a else null at the end without getting too deep in if/then/else constructs.
It is 100% case sensitive. So "7am" is not the same as "7AM"If you have minutes then this isn't workable like that. Otherwise, we'd need to see data. How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
Don't have access to the source data, can't figure out how to do in PQ since I'm stuck with DirectQuery. Calculated columns is the only way that I have been able to get this close. Not smart enough to figure out measures. I can get simple things like SUM to work but anything else and I'm stuck.
If you only have 24 values - 7am, 8am, 9am, etc for all 24 periods, you could create a custom column in Power Query to do this:
if [Column1] = "7am" then 1 else
if [Column1] = "8am" then 2 else
if [Column1] = "9am" then 3 else
nullThat is 3. You just need to do the other 21. There must be a else null at the end without getting too deep in if/then/else constructs.
It is 100% case sensitive. So "7am" is not the same as "7AM"
If you have minutes then this isn't workable like that. Otherwise, we'd need to see data. How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
This approach has got me as close as I think I can get. So I am going to Accept it.
For the whole story:
I have data from multiple machines. All the data is in two columns, column 1 is the machine # and column 2 is the time that the record was entered. There are 10 machines. When a machine produces a part, an entry in made in the table. So for example if machine 1 makes a part, there will be an entry of:
01 | 2020-12-29 9:03:02.343
If the next machine to make a part is 9, then there will be an entry of:
09 | 2020-12-29 9:03:02.500
And on like that. Each machine records it's name and time into the table.
What I want is an output that looks like:
Machine
Hour | 01|02|03|04|05|06|07|08|09|10
-----------------------------------------
7 | 23|13|45|32|10|17|33|19|10|11
8 | 10|13|45|32|10|17|33|19|10|11
9 | 23|13|34|32|10|22|33|19|10|11
10 | 23|13|45|32|10|17|33|19|10|11
11 | 23|13|45|32|10|32|33|19|10|11
12 | 23|18|45|32|10|17|33|19|10|11
13 | 23|13|45|32|10|23|33|19|10|11
14 | 23|13|45|45|10|17|33|19|10|11
15 | 23|13|45|32|10|17|33|19|10|11
16 | 23|13|45|32|10|17|33|19|10|11
17 | 23|14|45|32|10|11|33|19|10|11
18 | 23|13|21|32|10|12|33|19|10|11
19 | 23|23|45|32|10|18|33|19|10|11
20 | 23|13|45|32|10|17|33|19|10|11
21 | 31|13|45|32|10|17|33|19|10|11
22 | 23|13|45|35|10|17|33|19|10|11
23 | 23|13|22|32|10|17|21|19|10|11
0 | 23|13|45|32|10|17|33|19|10|11
1 | 23|13|45|31|10|12|33|19|10|11
2 | 23|32|45|32|10|22|33|19|10|11
3 | 23|13|13|32|10|17|19|19|10|11
4 | 32|13|45|32|10|17|33|19|10|11
5 | 23|13|45|32|10|17|33|11|10|11
6 | 23|13|45|32|10|17|33|19|10|11
Using Slicers for the Year, Month, and Day. The problem is that when you select the day, then the data in filtered to just that day. But our 24 hr shift, runs from 7:00 today through 7:00 tommorow.So my over-ridding issue is I need a way to select a day, but get part of the next day, also.
Hi pabeader , I would consider adding a 2nd date column, and then run your reports on that column. For example:
if DateTime.Time([Date]) < #time(7,0,0) then Date.AddDays(DateTime.Date([Date]), -1) else DateTime.Date([Date])So any times before 7am get a day subtracted. Any times on or after 7am stay in the current day. You could even then remove the original date and just keep the "New Date" field, or however you want to report it.
I think this breaks Direct Query...