Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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?
@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/
Hi,
Share data in a format that can be pasted in an Excel workbook.
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.
@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/
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"))