Forum Discussion
Merge Two Columns into One Column
Needs some cleanup on the initial column names.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZBLDsIwDETv0nWTjB3/suQCiH3V+1+DtGpQ+QiQvLCl8Zuxl2W6QcwT1wKCJSDVhASe5om5EBUG1z5INiAD6H14mFFAog9HrfNXFBXigeIsUbPogQpvKsb/oPw1VbXc3HMlHrGEGtM/rE1EUiAbS7b1/bxxorq4iNAuiwIbMjrLlFXZeny1z1b+sIqTFbXaqBla76/l8iOujy8+MRgqTS3eEOsd", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WBS = _t, #"Date MIGO-1" = _t, #"Value MIGO-1" = _t, #"Item MIGO-1" = _t, #"Date MIGO-2" = _t, #"Value MIGO-2" = _t, #"Item MIGO-2" = _t]), #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"WBS","Index"}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "")), #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}), #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute.1]), "Attribute.1", "Value") in #"Pivoted Column"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
Hello
Thanks for the help, I tested the code as you submitted it and it works. However, I was going to replicate it in the complete table to include other elements but it doesn't give me the same result.
Let
source = Excel.CurrentWorkbook(){[Name="Tabla_Control"]}[Content],
#"Type changed" = Table.TransformColumnTypes(Source,{{"PR", type any}, {"Requirement", type text}, {"WBS", type text}, {"IT", type text}, {"Non-Vendor", type any}, {"Vendor", type any}, {"Request Date", type any}, {"Purchase Order", type any}, {"PO Value", type number}, {"Currency", type text}, {"TRM", Int64.Type}, {"PO - LC Value", type number}, {"Creation Date", type datetime}, {"PO Delivery Date", type datetime}, {"Committed", type number}, {"Delivery Certificate", type any}, {"REAL Delivery Date", type any}, {"Remarks", type any}, {"Fecha_MIGO_1", type datetime}, {"Value #(lf)MIGO-1", type number}, {"MIGO - 1", type any}, {"Fecha_MIGO_2", type any}, {"Value #(lf)MIGO-2", type any}, {"MIGO - 2", type any}, {"Fecha_MIGO_3", type any}, {"Value #(lf)MIGO-3", type any}, {"MIGO - 3", type any}, {"Fecha_MIGO_4", type any}, {"Value #(lf)MIGO-4", type any}, {"MIGO - 4", type any}, {"Check Status#(lf)PO Expired", type text}, {"Check Status#(lf)PO Complete", type any}}),
#"Renamed Columns" = Table.RenameColumns(#"Type Changed",{{"PO Delivery Date", "Committed Date"}, {"Commited", "Committed Value"}, {"#(lf)MIGO-1 Value", "MIGO-1 Value"}, {"Fecha_MIGO_1", "MIGO-1 Date"}, {"Fecha_MIGO_2", "MIGO-2 Date"}, {"MIGO-2 Value", {"MIGO-2 Value"}, {"Fecha_MIGO_3", "MIGO-3 Date"}, {"MIGO-3 Value", {"MIGO-3 Value"}, {"Fecha_MIGO_4", "MIGO-4 Date"}, {"MIGO-4 Value", {"MIGO-4 Value"}}),
#"Type changed2" = Table.TransformColumnTypes(#"Renamed columns",{{"Date Commited", type date}, {"MIGO-1 date", type date}, {"MIGO-2 date", type date}, {"MIGO-3 date", type date}, {"MIGO-4 Date", type date}}),
#"Dynamic has been cancelled for selected columns only." = Table.Unpivot(#"Type changed2", {"Committed Value", "Committed Date", "MIGO-1 Date", "MIGO-1 Value", "MIGO-2 Date", "MIGO-2 Value", "MIGO-3 Date", "MIGO-3 Value", "MIGO-4 Date", "MIGO-4 Value"}, "Attribute", "Value"),
#"Filtered rows" = Table.SelectRows(#"Pivot has been unflowed for selected columns only.", each([Value] <> 0)),
#"Split column by delimiter" = Table.SplitColumn(#"Filtered rows", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
#"Pivot Column" = Table.Pivot(#"Type Changed1", List.Distinct(#"Type Changed1"[Attribute.1]), "Attribute.1", "Value", List.Count)
in
#"Dynamic column"
You know what I'm doing wrong?
See my note on the column cleanup. Your column names are inconsistent and must be aligned before they can be used.
for example instead of "MIGO - 3" you need to use "Item MIGO-3"