Forum Discussion

Stev_data's avatar
Stev_data
Frequent Visitor
4 years ago
Solved

Merge repeated row headers

Hi guys,

I have recently started using Powerquery and have managed to filter the data from an excel csv into the 'Current' table shown below. I am trying to change the 'Current' into the 'Expected Result' shown below

Current

Column4Column5Column8Column10Column13Column14Column15Value
       Defect : 181 POOR TRACK REPAIR Details :
File NoCustomerCct Defect Qty Batch Cct QtyBatch Cct Defect % Defect CostW/O No.
211571PS Limited1520 maj881-148-1-104
       Defect :181 POOR TRACK REPAIR Totals : 
       Defect : 182 POOR SHORT REMOVAL Details :
File NoCustomerCct Defect Qty Batch Cct QtyBatch Cct Defect % Defect CostW/O No.
211179BF Limited1185.555555556jap51.133333331380-863-1-106
211742KSLimited33010jmw53.71823-5-1-101
181031SK3545.555555556maj26.162088-1-14-102
211499QT Ltd43841.041666667jmw58.73625291-534-1-108
211654CMMLtd82433.33333333jmw509.566666782-151-1-104
211523SUTLtd1244.166666667jmw20.3733333393-230-1-102
201643IELtd16000.166666667maj5.641666667947-460-11-104
       Defect :182 POOR SHORT REMOVAL Totals : 
       Defect : 183 DAMAGED PAD / HOLE Details :
File NoCustomerCct Defect Qty Batch Cct QtyBatch Cct Defect % Defect CostW/O No.
181098AMLtd3329.375jdo48.66751145-145-4-104
081219RK Limited23600.555555556jdo3.261750-7-14-102
171394D Ltd4617282.662037037aoj32.969219951944-10-86-104
201652Amp21101.818181818jdo26.763636362288-7-23-102
201652Amp61105.454545455jdo80.290909092288-7-24-102
201255IE Ltd $28400.238095238sab4.2322764142375-1-8-106
151131IE Ltd $25400.37037037jdo7.742375-2-19-106
201438QT Ltd11080.925925926jdv32.41725218291-449-7-108
211555QT Ltd1362.777777778navg34.32291-536-1-108
211364BSSLtd.1422.380952381jdv33.03451045642-859-3-213
201345IELtd126400.037878788jdv1.241086627947-486-5-104
       Defect :183 DAMAGED PAD / HOLE Totals : 

 

Expected Result

File NoCustomerCct Defect Qty Batch Cct QtyBatch Cct Defect %OperatorDefect CostW/O No.DefectType 
211571PS Limited1520maj 881-148-1-104181 POOR TRACK REPAIR Details :
211179BF Limited1185.555555556jap51.133333331380-863-1-106182 POOR SHORT REMOVAL Details :
211742KSLimited33010jmw53.71823-5-1-101182 POOR SHORT REMOVAL Details :
181031SK3545.555555556maj26.162088-1-14-102182 POOR SHORT REMOVAL Details :
211499QT Ltd43841.041666667jmw58.73625291-534-1-108182 POOR SHORT REMOVAL Details :
211654CMMLtd82433.33333333jmw509.566666782-151-1-104182 POOR SHORT REMOVAL Details :
211523SUTLtd1244.166666667jmw20.3733333393-230-1-102182 POOR SHORT REMOVAL Details :
201643IELtd16000.166666667maj5.641666667947-460-11-104182 POOR SHORT REMOVAL Details :
181098AMLtd3329.375jdo48.66751145-145-4-104183 DAMAGED PAD / HOLE Details :
081219RK Limited23600.555555556jdo3.261750-7-14-102183 DAMAGED PAD / HOLE Details :
171394D Ltd4617282.662037037aoj32.969219951944-10-86-104183 DAMAGED PAD / HOLE Details :
201652Amp21101.818181818jdo26.763636362288-7-23-102183 DAMAGED PAD / HOLE Details :
201652Amp61105.454545455jdo80.290909092288-7-24-102183 DAMAGED PAD / HOLE Details :
201255IE Ltd $28400.238095238sab4.2322764142375-1-8-106183 DAMAGED PAD / HOLE Details :
151131IE Ltd $25400.37037037jdo7.742375-2-19-106183 DAMAGED PAD / HOLE Details :
201438QT Ltd11080.925925926jdv32.41725218291-449-7-108183 DAMAGED PAD / HOLE Details :
211555QT Ltd1362.777777778navg34.32291-536-1-108183 DAMAGED PAD / HOLE Details :
211364BSSLtd.1422.380952381jdv33.03451045642-859-3-213183 DAMAGED PAD / HOLE Details :
201345IELtd126400.037878788jdv1.241086627947-486-5-104183 DAMAGED PAD / HOLE Details :

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Stev_data,

    You can try to use the following M query codes if it was suitable for your requirement.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xVZNc9MwEP0rmg7csKrVt7ilSYBOW1KSUA6dHkwbIJmEMNSF4d+zu1Jsp5QZyqVW7JFs6enp7dudXF4eDLfru81Xe/Ci9Fzbi20PVNc1XbdbBLTqol7fLQ6uXlweCBz97R4tPi2uG/FSQARxPplMxXw6GJ6I6fh8cDwVo0VTL9e34iUDvVquF+Ltlva5u222m8V36uLygvKu+UWYR3Vz/UUM84u9cZn3fH/z4fa2wdGHwwmCS95JA7gA+PJ8Jk6Xm2WzuMEBvaCjaVUANvUKnzFCBTZW+FT2n0/88IHn26am84rHKKcz0OzNZDpHoLPJxeD0KaWDkGjtq3vSAVnISVcuj6NV/Y3egQSTL5pmoqqiN6yn32EGq/HbyayDpLmGIsF+XG1+EpKRgXfSpnIMAAyAYitDHGYnZaWzf5DJ0dRegucgRw6pRRC9Y2ETnezdXJw2RIEwTKQnSGXB0xU6MlEG4zU7JkHljGVGcQfmmcPw7CyDkTqaEY00nRwFSyXpWvyoK3DQMxz5VdPs2ft5RoMdmpWZV5+ZVtKEdodkKm0Uo5WDKvCWvhyPOzCvSGa1h5YVc9L3zp5sqKxHuMemw4Mu/q98MGI0OBu8Ho/E+WAkDsWbyen4yfKBrJcotoMSZ/YtmTlhEMgcqxviYaNEAWmMNkPv4m1bBVUEDWS96UkvqQjE+ByWvaxiQCM1DSA4VYW+jyGASWSN0c7GeZ5mByILrUzAH47q7YrZyuQTEkhML1nCwhTt7IeGccRmsPlWeAFnJcgIpbW8MMGCN9xopDHNAhpwz309MN+COWldbp1qUUmdFLcemO2DaZ5+PKbDimeFXrRZNo3FJmHqEL3b+iPnizZaB7Q0SaQxRJgZsS1FmHfAleQeoCuArFwWLzMMMrRAmLapK2oKLO/blhMukioyTtKOfzmeP3IULAbJadaSKoq1iSLbVRTHR93DyyLLUC5a+rX+8Zm+WMk2zLXJ79cm44n00WyGSLJAcQXWcqcYdMyMVMY6dANt762uoksVVhX8e1BOaqy7V0+0L4qhWJFabPFAaotc0IhtQUGzuUfWkwerQK+eXP0G", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}, {"(blank).6", type text}, {"(blank).7", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Defect", each if Text.Contains([#"(blank).7"],"Defect :") then Text.Trim(Text.RemoveRange([#"(blank).7"],0,8)) else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Defect"}),
    #"Removed Top Rows" = Table.Skip(#"Filled Down",2),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([File No] <> " " and [File No] <> "File No")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{" ", "Operator"}, {"181 POOR TRACK REPAIR Details :", "DefectType"}})
in
    #"Renamed Columns"


    Regards,

    Xiaoxin Sheng

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Stev_data,

    You can try to use the following M query codes if it was suitable for your requirement.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xVZNc9MwEP0rmg7csKrVt7ilSYBOW1KSUA6dHkwbIJmEMNSF4d+zu1Jsp5QZyqVW7JFs6enp7dudXF4eDLfru81Xe/Ci9Fzbi20PVNc1XbdbBLTqol7fLQ6uXlweCBz97R4tPi2uG/FSQARxPplMxXw6GJ6I6fh8cDwVo0VTL9e34iUDvVquF+Ltlva5u222m8V36uLygvKu+UWYR3Vz/UUM84u9cZn3fH/z4fa2wdGHwwmCS95JA7gA+PJ8Jk6Xm2WzuMEBvaCjaVUANvUKnzFCBTZW+FT2n0/88IHn26am84rHKKcz0OzNZDpHoLPJxeD0KaWDkGjtq3vSAVnISVcuj6NV/Y3egQSTL5pmoqqiN6yn32EGq/HbyayDpLmGIsF+XG1+EpKRgXfSpnIMAAyAYitDHGYnZaWzf5DJ0dRegucgRw6pRRC9Y2ETnezdXJw2RIEwTKQnSGXB0xU6MlEG4zU7JkHljGVGcQfmmcPw7CyDkTqaEY00nRwFSyXpWvyoK3DQMxz5VdPs2ft5RoMdmpWZV5+ZVtKEdodkKm0Uo5WDKvCWvhyPOzCvSGa1h5YVc9L3zp5sqKxHuMemw4Mu/q98MGI0OBu8Ho/E+WAkDsWbyen4yfKBrJcotoMSZ/YtmTlhEMgcqxviYaNEAWmMNkPv4m1bBVUEDWS96UkvqQjE+ByWvaxiQCM1DSA4VYW+jyGASWSN0c7GeZ5mByILrUzAH47q7YrZyuQTEkhML1nCwhTt7IeGccRmsPlWeAFnJcgIpbW8MMGCN9xopDHNAhpwz309MN+COWldbp1qUUmdFLcemO2DaZ5+PKbDimeFXrRZNo3FJmHqEL3b+iPnizZaB7Q0SaQxRJgZsS1FmHfAleQeoCuArFwWLzMMMrRAmLapK2oKLO/blhMukioyTtKOfzmeP3IULAbJadaSKoq1iSLbVRTHR93DyyLLUC5a+rX+8Zm+WMk2zLXJ79cm44n00WyGSLJAcQXWcqcYdMyMVMY6dANt762uoksVVhX8e1BOaqy7V0+0L4qhWJFabPFAaotc0IhtQUGzuUfWkwerQK+eXP0G", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}, {"(blank).6", type text}, {"(blank).7", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Defect", each if Text.Contains([#"(blank).7"],"Defect :") then Text.Trim(Text.RemoveRange([#"(blank).7"],0,8)) else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Defect"}),
    #"Removed Top Rows" = Table.Skip(#"Filled Down",2),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([File No] <> " " and [File No] <> "File No")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{" ", "Operator"}, {"181 POOR TRACK REPAIR Details :", "DefectType"}})
in
    #"Renamed Columns"


    Regards,

    Xiaoxin Sheng