Merge two columns from the same table

[Anonymous]

Greetings to the whole community, I present my consultation. I have a table with two columns of dates, one for income and one for egresses (table 1) but I would like to merge them into a single column as shown in the following image, how can I achieve this?

I thank you for your input

[Anonymous]

Greetings to the whole community, I present my consultation. I have a table with two columns of dates, one for income and one for egresses (table 1) but I would like to merge them into a single column as shown in the following image, how can I achieve this?

[amitchandak]

@Anonymous , consider pivot

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

or

https://www.thebiccountant.com/2015/08/12/how-to-pivot-multiple-measurescolumns-in-power-query/

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an Excel workbook.

[dax · Accepted solution]

Hi @Anonymous , 

You could refer to below M code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyipNzFPSUTIyMDLQN9Q3BDINDQxQBSwMlGJ1opUKEpNy8mEyZmAZI4RSc4heqNrkxKKc/GK4MRBzjJEMNoLoBymPBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [nombre = _t, #"fecha ingreso" = _t, ingreso = _t, #"fecha gasto" = _t, gasto = _t]),
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",{"Column2", "Column3"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Column4", "Column5"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged.1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Merged Columns1", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"nombre"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Value.1", "Value.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type date}, {"Value.2", Int64.Type}}),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Changed Type1", {{"Attribute", each Text.AfterDelimiter(_, ","), type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted Text After Delimiter",{{"Value.1", "monto"}, {"Attribute", "description"}, {"Value.2", "ingreso"}})
in
    #"Renamed Columns"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Try Unpivot

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

or

https://www.thebiccountant.com/2015/08/12/how-to-pivot-multiple-measurescolumns-in-power-query/

[Anonymous]

Hi @Anonymous 

Try to create a Calculated Table using this formula:

Table2 = UNION(
ADDCOLUMNS(SUMMARIZE(Table1,nombre, fecha ingreso, "monto", SUM(Table1[ingreso])),"descripcion", "ingreso"),
ADDCOLUMNS(SUMMARIZE(Table1,nombre, fecha ingreso, "monto", SUM(Table1[gasto])),"descripcion", "gasto"))