Merge repeating row headers

Hi, 

I haven't tried to solve the problem in the second row, defect cost, because i don't understand if it is  area problem or a question of how the data were displayed.

Beside that, this are the step you need to apply:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xVRNc9MwEP0rngzcalWrb/XmJintJCVpEuDQ6cG0AZJpSIeYMvx7dtefCZmhXMCx7EiWnp7evt3b297F6nGZvN32Tnr977tiu1l+o7/3RTJYflri66b4iQPneXH/Jekf6VfTXuMg3lWvv90V2PtwOkFo0bs7ue0pAOsBB6fzZLzarIrlA3ZowGJTsly/ydf4DAFSMCHFpzS8uvx6cFebnUGAZDqZzJLFLOuPktlwml1hZ1vkj7vk7M/rEwiqBJhfTmYLBLievM/GeLQiXzUQ/1Ao8JGWXhwIBYHUEtbWzWF/nT/xKIAWWnOjqTrINDjNEroa1xuF30bzFpbmatIe6LHe/CAsLTzvpnRqGQAYAGWWmnjMR9VKa44QKkOonADHgQ0cR4MwquZhIp3vZpGMCyJBKDrQE4Q0IJxz2HxLSASvhVNslAip1YZphRrPMZH+9XWJRzIpBq0VqVSp4WS0nU2CSsFCx2xkVUXz5+8WJSLUiAZP5X4jqITUvrNP1KnSkhGrM0twhr5cDVtAJ0lzSYh8+UY8ZLcnQzQ+NQ4BX5wQR/38Vxmhk0F2nb0ZDpJpNkhOk8vJePifMoKMFymoWRVgdi1ZOQrtyRXrB2JhAgrGfbQYOhebaQQLoIBcNxt1soowtCvDYKvLNXhaKOqAtzL1XQuDBx3JDoPaweU8xc7DoJWGwIb9fLsusaITUYGIkRlGQ3iYo63t0CSWGGWbp4obcFqCCBAEqkDvhh3u453QDjNDc6YpzDSPxttzXQfQNYBWGGuENfRu5QtCqoi5IUWUsQNouoCKF1wN6eDJq4pmMKWECqtOxNQhkrv8I+eL0kooJGqABFMYL8yL0FQlzDzgonIAaStI7SXfDUsvfAOEiRvb+ibB8M5NXeGaKQPjRGX5LqP7XEYEk8wrKxSrSpXFmEiRbiuL5ePuIZZiC+991Wjx1/z5M30zgn1ZVim3X6W0I+Ln8zliiQqMC7ISqJtA4fANHX5aagwQ1UQi4YxKg40p1hbQ9Ym1sQdVRblKORQt0C80iMAnlpQlqikraED70qpytCa0VeXuFw==", 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, #"Defect : 181 POOR TRACK REPAIR Details :" = _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}, {"Defect : 181 POOR TRACK REPAIR Details :", type text}}),
#"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
#"Added Conditional Column1" = Table.AddColumn(#"Demoted Headers", "Custom", each if Text.StartsWith([Column8], "Defect") then [Column8] else null),
#"Filled Down" = Table.FillDown(#"Added Conditional Column1",{"Custom"}),
#"Removed Top Rows" = Table.Skip(#"Filled Down",1),
#"Added Conditional Column2" = Table.AddColumn(#"Removed Top Rows", "Custom.1", each if Text.StartsWith([Column8], "W/O") then "Defect type" else [Custom]),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column2",{"Custom"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
#"Filtered Rows1" = Table.SelectRows(#"Promoted Headers", each ([File No] <> "" and [File No] <> "File No"))
in
#"Filtered Rows1"

The decimal and thousands signs depend on your local settings

Hi, 

These are the steps needed. Problably some final steps would not be necessary considering your native file.

let
Source =  ___________,
Foglio1_Sheet = Source{[Item="Foglio1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Foglio1_Sheet, [PromoteAllScalars=true]),
#"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"Column4", "Column7"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Current", type text}, {"Column2", type text}, {"Column3", type text}, {"Column5", type text}, {"Column6", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,"",Replacer.ReplaceValue,{"Current", "Column2", "Column3", "Column5", "Column6", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each [Current]&[Column2]&[Column3]&[Column5]&[Column6]&[Column8]&[Column9]&[Column10]&[Column11]&[Column12]&[Column13]),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] <> "")),
#"Removed Top Rows" = Table.Skip(#"Filtered Rows",1),
#"Filtered Rows1" = Table.SelectRows(#"Removed Top Rows", each not Text.Contains([Current], "Totals")),
#"Added Conditional Column" = Table.AddColumn(#"Filtered Rows1", "Custom.1", each if Text.StartsWith([Current], "DEPT") then [Current] else null),
#"Filled Up" = Table.FillUp(#"Added Conditional Column",{"Custom.1"}),
#"Added Conditional Column1" = Table.AddColumn(#"Filled Up", "Custom.2", each if Text.StartsWith([Current], "Defect") then [Current] else null),
#"Filled Down" = Table.FillDown(#"Added Conditional Column1",{"Custom.2"}),
#"Removed Top Rows1" = Table.Skip(#"Filled Down",5),
#"Filtered Rows3" = Table.SelectRows(#"Removed Top Rows1", each ([Current] <> "")),
#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows3", each not Text.Contains([Current], "TOTALS") and not Text.StartsWith([Current], "DEPT") and not Text.StartsWith([Current], "Defect") and not Text.StartsWith([Current], "Printed")),
#"Added Conditional Column2" = Table.AddColumn(#"Filtered Rows2", "Custom.3", each if Text.StartsWith([Current], "W/O") then "Defect" else [Custom.1]),
#"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "Custom.4", each if Text.StartsWith([Current], "W/O") then "DEPT" else [Custom.2]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column3",{"Custom", "Custom.1", "Custom.2"}),
#"Promoted Headers1" = Table.PromoteHeaders(#"Removed Columns1", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers1",{{"W/O No.", type text}, {"File No", type text}, {"Customer", type text}, {"", Int64.Type}, {"Cct Defect Qty", type text}, {"Batch Cct Qty", type text}, {"_1", type text}, {"Batch Cct Defect %", type text}, {"_2", type text}, {"_3", type text}, {"Defect Cost", type text}, {"Defect", type text}, {"DEPT", type text}}),
#"Filtered Rows4" = Table.SelectRows(#"Changed Type1", each ([#"W/O No."] <> "W/O No.")),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Filtered Rows4", {{"Defect", each Text.AfterDelimiter(_, ": "), type text}}),
#"Extracted Text After Delimiter1" = Table.TransformColumns(#"Extracted Text After Delimiter", {{"DEPT", each Text.AfterDelimiter(_, ": "), type text}}),
#"Replaced Value1" = Table.ReplaceValue(#"Extracted Text After Delimiter1"," Details :","",Replacer.ReplaceText,{"DEPT"}),
#"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Replaced Value1", {{"", type text}}, "it-IT"),{"", "Cct Defect Qty"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Cct Defect Qty.1"),
#"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"_1", "Batch Cct Defect %"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Batch Cct Defect %.1"),
#"Merged Columns" = Table.CombineColumns(#"Merged Columns2",{"_2", "_3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged")
in
#"Merged Columns"

If this post isuseful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !