Merge repeating row headers

[Stev_data]

Hi,

I need help to merge repeating row labels and place a wrongly positioned label into its own column

Current

Expected Result

The link to the information is attached below

https://docs.google.com/spreadsheets/d/e/2PACX-1vQl4__7u-xSYUuEPMYFjED7jMVeqYTdVNiaHeNY7UyaYrBOBs7Ic... 

[serpiva64 · Accepted solution]

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

[serpiva64 · Accepted solution]

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

[Stev_data]

Thank you @serpiva64.

I also have another example which I would appreciate help with.

I have an output from a database which is random with poorly aligned columns and row data. I want to align it into ordered form as shown in link. Any chance you could help or give some pointers as to how to approach it?

Current

Expected Result 

Here is the link showing the raw data as against the final desired result

https://docs.google.com/spreadsheets/d/e/2PACX-1vQl4__7u-xSYUuEPMYFjED7jMVeqYTdVNiaHeNY7UyaYrBOBs7Ic... 

[serpiva64 · Accepted solution]

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 !

[serpiva64]

Yes, I think it is possible. It only needs some time to elaborate it. I'm at a good point.

[serpiva64]

Hi, as soon as I'll have some time I'll try.

If the previous post was useful to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!