Power Query or Pivot formula request

nburaq

nburaq

Board Regular
Joined
Apr 2, 2021
Messages
220
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi,
I am working with office 2019 and I have datas based on year. In this data, there are years occured, event descriptions and finally lost hours.
As a rule, to call an event as a cause;
  • Same event should happen 4 times or more in that year. In this case, total lost hours might be less than 8 hours
  • Same event might happen less than 4 times in that year. In this case, total lost hours should be more than 8 hours
At the end, I would like to show how many cause do I have for that year and total lost hour in a pivot table.

Code can be written in Power Query as a "M" code or in Power Pivot as DAX formula.
Thanks again for your help and comments!


YearEvent DescriptionLost HourIs it accepted as Cause
2022Pump A is broken1YES
2022Pump A is broken1YES
2022Pump B is broken4YES
2022Pump B is broken4YES
2022Pump A is broken2YES
2022Pump A is broken1YES
2020Heater C is broken1NO
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
try:

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Lost Hour", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if[Is it accepted as Cause] ="YES" then 1 else 0),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Year"}, {{"LostHours", each List.Sum([Lost Hour]), type nullable number}, {"Total Cause", each List.Sum([Custom]), type number}})
in
    #"Grouped Rows"

Book2
ABC
11YearLostHoursTotal Cause
122022136
13202010
Sheet1
 
Upvote 0
alansidman said:
try:

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Lost Hour", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if[Is it accepted as Cause] ="YES" then 1 else 0),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Year"}, {{"LostHours", each List.Sum([Lost Hour]), type nullable number}, {"Total Cause", each List.Sum([Custom]), type number}})
in
    #"Grouped Rows"

Book2
ABC
11YearLostHoursTotal Cause
122022136
13202010
Sheet1
Click to expand...
Hi Alan,
You are truly power query master! Thanks for the solution but what I noticed that I made a mistake by adding the last column so basically the last column will show number of occurance (repeat)based on mentioned parameters.
I am able to count repeat values using countifs at excel but could not combine with number of occurance and 8 hours part

Yearevent desclost hour Repeat
2022Pump A is broken1
2022Pump A is broken1
2022Pump B is broken 4
2022Pump B is broken4
2022Pump A is broken2
2020Heater C is broken1
 
Upvote 0
I am not understanding what you want. Suggest you show us what your expected results would look like for the given situation. Am I to understand that you do not need the number of accepted causes?
 
Upvote 0
Hi Alan,
I am sorry that I could not explain clearly that's why I exported my query to excel and solved my problem with excel formula. The end result is shown at column L . I wish I could solve my problem directly with M code or with Power pivot. Thanks again and sorry for misunderstanding.

Formula is :

=IF(COUNTIFS(D:D;'UDT Analysis.xlsx'!UDTAll[@[Description ]];G:G;'UDT Analysis.xlsx'!UDTAll[@Year])>3;"Yes";IF(SUMIFS(E:E;D:D;'UDT Analysis.xlsx'!UDTAll[@[Description ]];G:G;'UDT Analysis.xlsx'!UDTAll[@Year])>7;"Yes";"No"))


Book1
ABCDEFGHIJK
1PlantDepartmentDescription Lost hoursLost TonsYearQuarterMonthMonth NameWeekCause
2SRIProductionLow Pressure Pump A2,6221202225May21No
3SRIProductionBroken Chain Drive B2,1182202225May21No
4SRIProductionBroken Chain Drive A0,868202225May20No
5SRIProductionBroken Chain Drive B1,5130202225May20No
6SRIProductionA1234TT0,974202225May20No
7SRIProductionA1234TT0,14202225May20No
8SRIProductionB758PT0,642202225May20No
9SRIProductionB758PT0,14202225May20No
10SRIProductionSDT0,323202225May20No
11SRIMaintenanceSDT0,426202225May20No
12SRIProductionBroken Roller0,217202225May20No
13SRIProductionBroken Pump A2148202225May20No
14SRIProductionBroken Pump A0,429202225May20No
15SRIProductionGrinder B dwon189202225May20No
16SRIProductionDowntime 0,213202225May20No
17SRIMaintenanceDowntime 0,19202225May20No
18SRIProductionGrinder B dwon03202225May20No
19SRIProductionB758PT0,329202225May20No
20SRIProductionStart up after downtime0,215202225May20No
21SRIProductionLow Temperature Heater A1,4122202225May20No
22SRIOtherSdt17,11,265202225May20Yes
23SRIProductionLow Temperature Heater A02202225May20No
24SRIProductionBroken Roller0,649202225May20No
25SRIOtherSdt241,775202225May20Yes
26SRIProductionBroken Bolt Pump A03202225May20No
27SRIProductionBroken Bolt Pump A1,6138202225May20No
28SRIProductionValve problem 0,112202225May20No
29SRIOtherValve problem 0,110202225May20No
30SRIOtherSDT16,71,234202225May20Yes
31SRIMaintenanceBroken Bolt Pump A185202225May20No
32SRIProductionA1234TT03202225May20No
33SRIProductionTurbine failure0,324202225May20No
34SRIProductionStart up after downtime1,191202225May20No
35SRIProductionTurbine failure0,763202225May19No
36SRIOtherNo power1,195202225May19No
37SRIOtherNo power0,978202225May19No
Sheet1
Cell Formulas
RangeFormula
K2:K37K2=IF(COUNTIFS(C:C,'UDT Analysis.xlsx'!UDTAll[@[Description ]],F:F,'UDT Analysis.xlsx'!UDTAll[@Year])>3,"Yes",IF(SUMIFS(D:D,C:C,'UDT Analysis.xlsx'!UDTAll[@[Description ]],F:F,'UDT Analysis.xlsx'!UDTAll[@Year])>7,"Yes","No"))
 
Upvote 0
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Lost hours", type number}}),
    Group = Table.Group(Type, {"Description  ", "Year"}, {{"Lost Hours", each _}}),
    Reorder = Table.ReorderColumns(Group,{"Year", "Description  ", "Lost Hours"}),
    AddCol = Table.AddColumn(Reorder, "Instances", each Table.RowCount([Lost Hours])),
    Result = Table.TransformColumns(AddCol,{{"Lost Hours", each List.Sum(_[Lost hours])}})
in
    Result

Book2
ABCDEFGHIJKLMNOPQ
1PlantDepartmentDescription Lost hoursLost TonsYearQuarterMonthMonth NameWeekCauseYearDescription Lost HoursInstances
2SRIProductionLow Pressure Pump A2.6221202225May21No2022Low Pressure Pump A2.61
3SRIProductionBroken Chain Drive B2.1182202225May21No2022Broken Chain Drive B3.62
4SRIProductionBroken Chain Drive A0.868202225May20No2022Broken Chain Drive A0.81
5SRIProductionBroken Chain Drive B1.5130202225May20No2022A1234TT13
6SRIProductionA1234TT0.974202225May20No2022B758PT13
7SRIProductionA1234TT0.14202225May20No2022SDT17.43
8SRIProductionB758PT0.642202225May20No2022Broken Roller0.82
9SRIProductionB758PT0.14202225May20No2022Broken Pump A2.42
10SRIProductionSDT0.323202225May20No2022Grinder B dwon12
11SRIMaintenanceSDT0.426202225May20No2022Downtime 0.32
12SRIProductionBroken Roller0.217202225May20No2022Start up after downtime1.32
13SRIProductionBroken Pump A2148202225May20No2022Low Temperature Heater A1.42
14SRIProductionBroken Pump A0.429202225May20No2022Sdt41.12
15SRIProductionGrinder B dwon189202225May20No2022Broken Bolt Pump A2.63
16SRIProductionDowntime 0.213202225May20No2022Valve problem 0.22
17SRIMaintenanceDowntime 0.19202225May20No2022Turbine failure12
18SRIProductionGrinder B dwon03202225May20No2022No power22
19SRIProductionB758PT0.329202225May20No
20SRIProductionStart up after downtime0.215202225May20No
21SRIProductionLow Temperature Heater A1.4122202225May20No
22SRIOtherSdt17.11.265202225May20No
23SRIProductionLow Temperature Heater A02202225May20No
24SRIProductionBroken Roller0.649202225May20No
25SRIOtherSdt241.775202225May20No
26SRIProductionBroken Bolt Pump A03202225May20No
27SRIProductionBroken Bolt Pump A1.6138202225May20No
28SRIProductionValve problem 0.112202225May20No
29SRIOtherValve problem 0.110202225May20No
30SRIOtherSDT16.71.234202225May20No
31SRIMaintenanceBroken Bolt Pump A185202225May20No
32SRIProductionA1234TT03202225May20No
33SRIProductionTurbine failure0.324202225May20No
34SRIProductionStart up after downtime1.191202225May20No
35SRIProductionTurbine failure0.763202225May19No
36SRIOtherNo power1.195202225May19No
37SRIOtherNo power0.978202225May19No
38
Sheet1
Cell Formulas
RangeFormula
K2:K37K2=IF(COUNTIFS(C:C,'UDT Analysis.xlsx'!UDTAll[@[Description ]],F:F,'UDT Analysis.xlsx'!UDTAll[@Year])>3,"Yes",IF(SUMIFS(D:D,C:C,'UDT Analysis.xlsx'!UDTAll[@[Description ]],F:F,'UDT Analysis.xlsx'!UDTAll[@Year])>7,"Yes","No"))
 
Upvote 0
JGordon11 said:
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Lost hours", type number}}),
    Group = Table.Group(Type, {"Description  ", "Year"}, {{"Lost Hours", each _}}),
    Reorder = Table.ReorderColumns(Group,{"Year", "Description  ", "Lost Hours"}),
    AddCol = Table.AddColumn(Reorder, "Instances", each Table.RowCount([Lost Hours])),
    Result = Table.TransformColumns(AddCol,{{"Lost Hours", each List.Sum(_[Lost hours])}})
in
    Result

Book2
ABCDEFGHIJKLMNOPQ
1PlantDepartmentDescription Lost hoursLost TonsYearQuarterMonthMonth NameWeekCauseYearDescription Lost HoursInstances
2SRIProductionLow Pressure Pump A2.6221202225May21No2022Low Pressure Pump A2.61
3SRIProductionBroken Chain Drive B2.1182202225May21No2022Broken Chain Drive B3.62
4SRIProductionBroken Chain Drive A0.868202225May20No2022Broken Chain Drive A0.81
5SRIProductionBroken Chain Drive B1.5130202225May20No2022A1234TT13
6SRIProductionA1234TT0.974202225May20No2022B758PT13
7SRIProductionA1234TT0.14202225May20No2022SDT17.43
8SRIProductionB758PT0.642202225May20No2022Broken Roller0.82
9SRIProductionB758PT0.14202225May20No2022Broken Pump A2.42
10SRIProductionSDT0.323202225May20No2022Grinder B dwon12
11SRIMaintenanceSDT0.426202225May20No2022Downtime 0.32
12SRIProductionBroken Roller0.217202225May20No2022Start up after downtime1.32
13SRIProductionBroken Pump A2148202225May20No2022Low Temperature Heater A1.42
14SRIProductionBroken Pump A0.429202225May20No2022Sdt41.12
15SRIProductionGrinder B dwon189202225May20No2022Broken Bolt Pump A2.63
16SRIProductionDowntime 0.213202225May20No2022Valve problem 0.22
17SRIMaintenanceDowntime 0.19202225May20No2022Turbine failure12
18SRIProductionGrinder B dwon03202225May20No2022No power22
19SRIProductionB758PT0.329202225May20No
20SRIProductionStart up after downtime0.215202225May20No
21SRIProductionLow Temperature Heater A1.4122202225May20No
22SRIOtherSdt17.11.265202225May20No
23SRIProductionLow Temperature Heater A02202225May20No
24SRIProductionBroken Roller0.649202225May20No
25SRIOtherSdt241.775202225May20No
26SRIProductionBroken Bolt Pump A03202225May20No
27SRIProductionBroken Bolt Pump A1.6138202225May20No
28SRIProductionValve problem 0.112202225May20No
29SRIOtherValve problem 0.110202225May20No
30SRIOtherSDT16.71.234202225May20No
31SRIMaintenanceBroken Bolt Pump A185202225May20No
32SRIProductionA1234TT03202225May20No
33SRIProductionTurbine failure0.324202225May20No
34SRIProductionStart up after downtime1.191202225May20No
35SRIProductionTurbine failure0.763202225May19No
36SRIOtherNo power1.195202225May19No
37SRIOtherNo power0.978202225May19No
38
Sheet1
Cell Formulas
RangeFormula
K2:K37K2=IF(COUNTIFS(C:C,'UDT Analysis.xlsx'!UDTAll[@[Description ]],F:F,'UDT Analysis.xlsx'!UDTAll[@Year])>3,"Yes",IF(SUMIFS(D:D,C:C,'UDT Analysis.xlsx'!UDTAll[@[Description ]],F:F,'UDT Analysis.xlsx'!UDTAll[@Year])>7,"Yes","No"))
Click to expand...
Hi J,
Thanks for your help but what I would like to have as end result is to add a column "Cause" to my queries based on the conditions below;
  • Same event should happen 4 times or more in that year. In this case, total lost hours might be less than 8 hours
  • Same event might happen less than 4 times in that year. In this case, total lost hours should be more than 8 hours
I have managed to solve this problem in power pivot by adding calculated column

Cause (Calculated) =
VAR CaseYearTable = CALCULATETABLE ( Incidents, ALLEXCEPT ( Incidents, Incidents[Department],Incidents[Description], Incidents[Year] ) )
VAR AnnualOccurrences = COUNTROWS ( CaseYearTable )
VAR TotalLostHours = Incidents[Lost hours]
RETURN
IF ( AnnualOccurrences >= 4 || TotalLostHours >= 8, "Yes", "No" )

My question was, is it possible to have the same result with power query?
 
Upvote 0
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Lost hours", type number}}),
    Uppercase = Table.TransformColumns(Type,{{"Description", Text.Upper, type text}}),
    Group = Table.Group(Uppercase, {"Description", "Year"}, {{"Lost Hours", each _}}),
    Reorder = Table.ReorderColumns(Group,{"Year", "Description", "Lost Hours"}),
    AddCol = Table.AddColumn(Reorder, "Instances", each Table.RowCount([Lost Hours])),
    SummaryTable = Table.TransformColumns(AddCol,{{"Lost Hours", each List.Sum(_[Lost hours])}}),
    Result = Table.AddColumn(Uppercase,"Cause", (x)=> let t=Table.SelectRows(SummaryTable, each _[Description] = x[Description] and _[Year] = x[Year] ) in if t[Lost Hours]{0}>=8 or t[Instances]{0}>=4 
                then "Yes" else "No")
in
    Result

Book2
ABCDEFGHIJKLMNOPQRSTUVW
1PlantDepartmentDescriptionLost hoursLost TonsYearQuarterMonthMonth NameWeekPlantDepartmentDescriptionLost hoursLost TonsYearQuarterMonthMonth NameWeekCause
2SRIProductionLow Pressure Pump A2.6221202225May21SRIProductionLOW PRESSURE PUMP A2.6221202225May21No
3SRIProductionBroken Chain Drive B2.1182202225May21SRIProductionBROKEN CHAIN DRIVE B2.1182202225May21No
4SRIProductionBroken Chain Drive A0.868202225May20SRIProductionBROKEN CHAIN DRIVE A0.868202225May20No
5SRIProductionBroken Chain Drive B1.5130202225May20SRIProductionBROKEN CHAIN DRIVE B1.5130202225May20No
6SRIProductionA1234TT0.974202225May20SRIProductionA1234TT0.974202225May20No
7SRIProductionA1234TT0.14202225May20SRIProductionA1234TT0.14202225May20No
8SRIProductionB758PT0.642202225May20SRIProductionB758PT0.642202225May20No
9SRIProductionB758PT0.14202225May20SRIProductionB758PT0.14202225May20No
10SRIProductionSDT0.323202225May20SRIProductionSDT0.323202225May20Yes
11SRIMaintenanceSDT0.426202225May20SRIMaintenanceSDT0.426202225May20Yes
12SRIProductionBroken Roller0.217202225May20SRIProductionBROKEN ROLLER0.217202225May20No
13SRIProductionBroken Pump A2148202225May20SRIProductionBROKEN PUMP A2148202225May20No
14SRIProductionBroken Pump A0.429202225May20SRIProductionBROKEN PUMP A0.429202225May20No
15SRIProductionGrinder B dwon189202225May20SRIProductionGRINDER B DWON189202225May20No
16SRIProductionDowntime 0.213202225May20SRIProductionDOWNTIME 0.213202225May20No
17SRIMaintenanceDowntime 0.19202225May20SRIMaintenanceDOWNTIME 0.19202225May20No
18SRIProductionGrinder B dwon03202225May20SRIProductionGRINDER B DWON03202225May20No
19SRIProductionB758PT0.329202225May20SRIProductionB758PT0.329202225May20No
20SRIProductionStart up after downtime0.215202225May20SRIProductionSTART UP AFTER DOWNTIME0.215202225May20No
21SRIProductionLow Temperature Heater A1.4122202225May20SRIProductionLOW TEMPERATURE HEATER A1.4122202225May20No
22SRIOtherSdt17.11.265202225May20SRIOtherSDT17.11.265202225May20Yes
23SRIProductionLow Temperature Heater A02202225May20SRIProductionLOW TEMPERATURE HEATER A02202225May20No
24SRIProductionBroken Roller0.649202225May20SRIProductionBROKEN ROLLER0.649202225May20No
25SRIOtherSdt241.775202225May20SRIOtherSDT241.775202225May20Yes
26SRIProductionBroken Bolt Pump A03202225May20SRIProductionBROKEN BOLT PUMP A03202225May20No
27SRIProductionBroken Bolt Pump A1.6138202225May20SRIProductionBROKEN BOLT PUMP A1.6138202225May20No
28SRIProductionValve problem 0.112202225May20SRIProductionVALVE PROBLEM 0.112202225May20No
29SRIOtherValve problem 0.110202225May20SRIOtherVALVE PROBLEM 0.110202225May20No
30SRIOtherSDT16.71.234202225May20SRIOtherSDT16.71.234202225May20Yes
31SRIMaintenanceBroken Bolt Pump A185202225May20SRIMaintenanceBROKEN BOLT PUMP A185202225May20No
32SRIProductionA1234TT03202225May20SRIProductionA1234TT03202225May20No
33SRIProductionTurbine failure0.324202225May20SRIProductionTURBINE FAILURE0.324202225May20No
34SRIProductionStart up after downtime1.191202225May20SRIProductionSTART UP AFTER DOWNTIME1.191202225May20No
35SRIProductionTurbine failure0.763202225May19SRIProductionTURBINE FAILURE0.763202225May19No
36SRIOtherNo power1.195202225May19SRIOtherNO POWER1.195202225May19No
37SRIOtherNo power0.978202225May19SRIOtherNO POWER0.978202225May19No
38
Sheet1
 
Upvote 0
Solution
JGordon11 said:
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Lost hours", type number}}),
    Uppercase = Table.TransformColumns(Type,{{"Description", Text.Upper, type text}}),
    Group = Table.Group(Uppercase, {"Description", "Year"}, {{"Lost Hours", each _}}),
    Reorder = Table.ReorderColumns(Group,{"Year", "Description", "Lost Hours"}),
    AddCol = Table.AddColumn(Reorder, "Instances", each Table.RowCount([Lost Hours])),
    SummaryTable = Table.TransformColumns(AddCol,{{"Lost Hours", each List.Sum(_[Lost hours])}}),
    Result = Table.AddColumn(Uppercase,"Cause", (x)=> let t=Table.SelectRows(SummaryTable, each _[Description] = x[Description] and _[Year] = x[Year] ) in if t[Lost Hours]{0}>=8 or t[Instances]{0}>=4
                then "Yes" else "No")
in
    Result

Book2
ABCDEFGHIJKLMNOPQRSTUVW
1PlantDepartmentDescriptionLost hoursLost TonsYearQuarterMonthMonth NameWeekPlantDepartmentDescriptionLost hoursLost TonsYearQuarterMonthMonth NameWeekCause
2SRIProductionLow Pressure Pump A2.6221202225May21SRIProductionLOW PRESSURE PUMP A2.6221202225May21No
3SRIProductionBroken Chain Drive B2.1182202225May21SRIProductionBROKEN CHAIN DRIVE B2.1182202225May21No
4SRIProductionBroken Chain Drive A0.868202225May20SRIProductionBROKEN CHAIN DRIVE A0.868202225May20No
5SRIProductionBroken Chain Drive B1.5130202225May20SRIProductionBROKEN CHAIN DRIVE B1.5130202225May20No
6SRIProductionA1234TT0.974202225May20SRIProductionA1234TT0.974202225May20No
7SRIProductionA1234TT0.14202225May20SRIProductionA1234TT0.14202225May20No
8SRIProductionB758PT0.642202225May20SRIProductionB758PT0.642202225May20No
9SRIProductionB758PT0.14202225May20SRIProductionB758PT0.14202225May20No
10SRIProductionSDT0.323202225May20SRIProductionSDT0.323202225May20Yes
11SRIMaintenanceSDT0.426202225May20SRIMaintenanceSDT0.426202225May20Yes
12SRIProductionBroken Roller0.217202225May20SRIProductionBROKEN ROLLER0.217202225May20No
13SRIProductionBroken Pump A2148202225May20SRIProductionBROKEN PUMP A2148202225May20No
14SRIProductionBroken Pump A0.429202225May20SRIProductionBROKEN PUMP A0.429202225May20No
15SRIProductionGrinder B dwon189202225May20SRIProductionGRINDER B DWON189202225May20No
16SRIProductionDowntime 0.213202225May20SRIProductionDOWNTIME 0.213202225May20No
17SRIMaintenanceDowntime 0.19202225May20SRIMaintenanceDOWNTIME 0.19202225May20No
18SRIProductionGrinder B dwon03202225May20SRIProductionGRINDER B DWON03202225May20No
19SRIProductionB758PT0.329202225May20SRIProductionB758PT0.329202225May20No
20SRIProductionStart up after downtime0.215202225May20SRIProductionSTART UP AFTER DOWNTIME0.215202225May20No
21SRIProductionLow Temperature Heater A1.4122202225May20SRIProductionLOW TEMPERATURE HEATER A1.4122202225May20No
22SRIOtherSdt17.11.265202225May20SRIOtherSDT17.11.265202225May20Yes
23SRIProductionLow Temperature Heater A02202225May20SRIProductionLOW TEMPERATURE HEATER A02202225May20No
24SRIProductionBroken Roller0.649202225May20SRIProductionBROKEN ROLLER0.649202225May20No
25SRIOtherSdt241.775202225May20SRIOtherSDT241.775202225May20Yes
26SRIProductionBroken Bolt Pump A03202225May20SRIProductionBROKEN BOLT PUMP A03202225May20No
27SRIProductionBroken Bolt Pump A1.6138202225May20SRIProductionBROKEN BOLT PUMP A1.6138202225May20No
28SRIProductionValve problem 0.112202225May20SRIProductionVALVE PROBLEM 0.112202225May20No
29SRIOtherValve problem 0.110202225May20SRIOtherVALVE PROBLEM 0.110202225May20No
30SRIOtherSDT16.71.234202225May20SRIOtherSDT16.71.234202225May20Yes
31SRIMaintenanceBroken Bolt Pump A185202225May20SRIMaintenanceBROKEN BOLT PUMP A185202225May20No
32SRIProductionA1234TT03202225May20SRIProductionA1234TT03202225May20No
33SRIProductionTurbine failure0.324202225May20SRIProductionTURBINE FAILURE0.324202225May20No
34SRIProductionStart up after downtime1.191202225May20SRIProductionSTART UP AFTER DOWNTIME1.191202225May20No
35SRIProductionTurbine failure0.763202225May19SRIProductionTURBINE FAILURE0.763202225May19No
36SRIOtherNo power1.195202225May19SRIOtherNO POWER1.195202225May19No
37SRIOtherNo power0.978202225May19SRIOtherNO POWER0.978202225May19No
38
Sheet1
Click to expand...
Thanks a lot! it is perfect! I knew that this someone can achieve this via M code.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Power Query refresh taking HOURS after adding custom column
Replies
3
Views
271
Matt Allington
M
D
  • Question
Power Query - add custom formula to data set
Replies
1
Views
602
CSmith
CSmith
D
  • Solved
SumProduct in Excel's Power Query
Replies
9
Views
616
JGordon11
JGordon11
S
  • Question
Power Query looking for item not in list
Replies
1
Views
286
Matt Allington
M
T
  • Question
slow measure needs help!
Replies
0
Views
546
tonysharpe
T

Forum statistics

Threads
1,214,653
Messages
6,120,750
Members
448,989
Latest member
mariah3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top