Append columns within a table

[Anonymous]

Hi,

I have a table like this: 

ID	Forecast1	Forecast2	Forecast3	Actual1	Actual2	Actual3
1	A	B	C	D	E	F
2	G	H	J	K	L	M

And I want to make it like below in Power Query Editor. Is there any way of doing this without having multiple duplicate tables and appending them again.  Thanks for the help!

ID	Forecast	Actual	End Num
1	A	D	1
1	B	E	2
1	C	F	3
2	G	K	1
2	H	L	2
2	J	M	3

[mwegener · Accepted solution]

Hi @Anonymous ,

try this. PBIX

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("DcY5DQAgEEVBL7+mAQfchEPBZv3b4BWTjJmigjIKKho6hjyYEptY2Di4eHL/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Forecast1 = _t, Forecast2 = _t, Forecast3 = _t, Actual1 = _t, Actual2 = _t, Actual3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Forecast1", type text}, {"Forecast2", type text}, {"Forecast3", type text}, {"Actual1", type text}, {"Actual2", type text}, {"Actual3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Attribute.1", "Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Character Transition", List.Distinct(#"Split Column by Character Transition"[Attribute.1]), "Attribute.1", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute.2", "End Num"}})
in
    #"Renamed Columns"

[kentyler]

Can you tell us what the goal is.

This seems like one of those cases where it makes more sense to manipulate the data before you load it into Power BI.

The column of "end numbers" means that you are going to be making 3 passes over the base data, and keeping track of the "number" of the pass.

There are M code functions that allow you to create new rows.... but it would be easier to work out if we knew what you were trying to achieve.