Merge Two Columns into One Column

Hello:

I have the following data table:

WBS	Date MIGO-1	Value MIGO-1	MIGO - 1	Date MIGO-2	Value MIGO-2	MIGO - 2
P0467-23/0106-00-3-0-02	22/11/2023	4.600.000	8786618048
P0467-23/0106-00-3-0-02	21/12/2023	2.483.450	8788795462
P0467-23/0106-00-3-0-07	22/11/2023	36.977.312	8786641921
P0467-23/0106-00-3-0-01	14/04/2024	80.000.000	8574744411	18/06/2024	10.000.000	52552654656
P0467-23/0106-00-7-0-01	18/04/2024	193919609	N/A
P0467-23/0106-00-7-0-02	18/04/2024	20549568	N/A

And I need to be able to combine the information in a single column, the date information, and the value information, so that I can group by WBS the total per month of what was paid.

Try transposing the date and column values by doing two separate operations and the result is this:

where you are duplicating the values that are highlighted in yellow and the final value of the MIGO sum is not correct.

So I did it in a single transposing:

Here the value is correct but since the date and value are in the same column I can't adjust the date format individually to be able to organize it chronologically:

I was thinking of making a conditional row that copied the value of the date and I put this new row in the correct format. I would like to know how it is done or what suggestions you have for this table.

Thank you.

lbendlin

2 years ago

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.

3 Replies

lbendlin
Super User
2 years ago
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.
- Syndicate_Admin
  Administrator
  2 years ago
  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?
  - lbendlin
    Super User
    2 years ago
    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"

Forum Discussion

Merge Two Columns into One Column

3 Replies

Related content

Merge Column

Merge duplicates data in columns

Merge columns keep null

Issues with Merged Column

New Column