Unpivot for changing table

[uda123]

Hi guys,

I have a table that look something like this:

Company ID	Report1	Income	Expensses	profit	Report2	Income	Expensses	profit	Report3	Income	Expensses	profit
11111	01/01/2021	10	5	5	01/01/2022	15	10	5	01/01/2023	10	1	9
22222	01/03/2021	11	5	6	01/03/2022	16	10	6	01/03/2023	12	5	7
33333	01/05/2021	12	5	7	01/05/2022	17	10	7	01/05/2023	14	9	5

As you can see, every report have 3 columns after it. 

I wanted to unpivot it and make the table more comfortable to work it, but the customer said that every year he will add 4 new columns. I  built for him a new template to work with but he doesnt want to use it.

How can I unpivot this table automatically without do it again next year (because he will add new columns)?

[amitchandak]

@uda123 , CHeck if this power query Script code can help

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY7bCQAhDAR7ybdwSXxxtYj9t3FuiDkNKujsDo5BgqFELM9ayoqL8Dqq7yAKUk8cKO9XtF+aaZBiPJNDLN5sJzFx24oLmVi91E2cMZ6pIf4zBzFx3+ILmbjYb1Gc8wM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Company ID" = _t, Report1 = _t, Income = _t, Expensses = _t, profit = _t, Report2 = _t, Income.1 = _t, Expensses.1 = _t, profit.1 = _t, Report3 = _t, Income.2 = _t, Expensses.2 = _t, profit.2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company ID", Int64.Type}, {"Report1", type date}, {"Income", Int64.Type}, {"Expensses", Int64.Type}, {"profit", Int64.Type}, {"Report2", type date}, {"Income.1", Int64.Type}, {"Expensses.1", Int64.Type}, {"profit.1", Int64.Type}, {"Report3", type date}, {"Income.2", Int64.Type}, {"Expensses.2", Int64.Type}, {"profit.2", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Company ID"}, "Attribute", "Value"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Attribute.1", "Attribute.2"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Character Transition",".","",Replacer.ReplaceText,{"Attribute.1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"1",Replacer.ReplaceValue,{"Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value1", List.Distinct(#"Replaced Value1"[Attribute.1]), "Attribute.1", "Value", List.Max)
in
    #"Pivoted Column"