Forum Discussion
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 R...
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
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