Unpivot with relative information in column titles

[bkr]

Hi all,

I have a weird data source in which the column names give years relative to another column:

Reference Year	Sales Reference Year -1	Sales Reference Year	Sales Reference Year +1	Costs Reference Year -1	Costs Reference Year	Costs Reference Year +1
2020	10	11	12	8	9	10
2023	15	16	15	12	13	14
2027	20	19	18	16	16	15

I would like to unpivot this and convert it to absolute years. The expected result should look like this:

Year	Sales	Costs
2019	10	8
2020	11	9
2021	12	10
2022	15	12
2023	16	13
2024	15	14
2025	0	0
2026	20	16
2027	19	16
2028	15	15

Ireally have no clue how to address this. Any ideas? Thanks!

[artemus · Accepted solution]

It would look like (Change Table to your data source):

let
Source = Table,
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Reference Year"}, "Attribute", "Value"),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Unpivoted Other Columns", "Category", each Text.BeforeDelimiter([Attribute], " Reference"), type text),
#"Inserted Text After Delimiter" = Table.AddColumn(#"Inserted Text Before Delimiter", "Year Offset", each Text.AfterDelimiter([Attribute], "Year "), type text),
#"Replaced Value" = Table.ReplaceValue(#"Inserted Text After Delimiter","","0",Replacer.ReplaceValue,{"Year Offset"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Year Offset", Int64.Type}}),
#"Inserted Addition" = Table.AddColumn(#"Changed Type", "Year", each [Reference Year] + [Year Offset], Int64.Type),
#"Removed Other Columns" = Table.SelectColumns(#"Inserted Addition",{"Year", "Category", "Value"}),
#"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Category]), "Category", "Value", List.Sum)
in
#"Pivoted Column"

[HotChilli]

Here's my advanced editor code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlDSUTIEE4YgwghIWACxJUQ4VgesyBjEMwURZnAWSKUhWMIEpswcyIMYCNZuAdcA1RUbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Reference Year" = _t, #"Sales Reference Year -1" = _t, #"Sales Reference Year" = _t, #"Sales Reference Year +1" = _t, #"Costs Reference Year -1" = _t, #"Costs Reference Year" = _t, #"Costs Reference Year +1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reference Year", Int64.Type}, {"Sales Reference Year -1", Int64.Type}, {"Sales Reference Year", Int64.Type}, {"Sales Reference Year +1", Int64.Type}, {"Costs Reference Year -1", Int64.Type}, {"Costs Reference Year", Int64.Type}, {"Costs Reference Year +1", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Reference Year"}, "Attribute", "Value"),
    #"Split Column by Position" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByPositions({0, 20}, false), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,0,Replacer.ReplaceValue,{"Attribute.2"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "CalculatedYear", each [Reference Year] + [Attribute.2]),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Attribute.1]), "Attribute.1", "Value", List.Sum)
in
    #"Pivoted Column"

I unpivoted all columns apart from Reference Year.

Split the column to isolate the -1, +1

Replace the null with 0.  Change the type to whole number (which preserves the + , -  thankfully)

Add a column which adds the year and the offset.

Pivot the column which has the required headings.

I didn't add in the missing year but you could do this by making a table with 2 columns.  First would be a year, with a list of year numbers e.g. 1950- 2050.  The second column would be 0.

Then join the above pivoted table with the new table created on the Year.  Something like that.

[bkr]

Thank you, I believe this is correct but I found the other answer a bit easier to understand.