Power Query to Unpivot Single Header Field

[legalhustler]

I have a header row in a single field in column 1 in the following format:

Scenario: Actual Year: 2018 Period: Jan Value: <(Entity)>

Below this I have my normal data columns.

How can I unpivot this single top header field so that I have just the Year (2018) filling down one column and the Period (Jan) filling down another column?

 

[legalhustler]

I have a header row in a single field in column 1 in the following format:

Scenario: Actual Year: 2018 Period: Jan Value: <(Entity)>

Below this I have my normal data columns.

How can I unpivot this single top header field so that I have just the Year (2018) filling down one column and the Period (Jan) filling down another column?

UPDATE:

I was able to unpivot the single field so that row 1 shows "2018" and row 2 shows "Jan"; from the 3rd row is my normal table columns. How can I get row 1 with value "2018" to fill down in one column and row 2 with value "Jan" fill down in a second column?

 

[Norie]

How many columns in the actual data?

 

[legalhustler]

How many columns in the actual data?

21 columns. These 21 columns are under the two headers mentioned above. Just need to have the two headers from row 1 and 2 display as columns filled down so that I end up having a total of 23 columns.

 

[Norie]

You could do this in Power Query but the process is rather convoluted.

When I tried it took 14 steps, though admittedly some of them probably aren't required.

It would probably be easier to do it with a short piece of code, or even manually.

Anyway, for what it's worth here's the M code for the steps I took.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Scenario: Actual Year: 2018 Period: Jan Value: <(Entity)>", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20"}, "Attribute", "Value"),
    #"Reordered Columns" = Table.ReorderColumns(#"Unpivoted Columns",{"Attribute", "Value", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "Attribute", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Attribute.1", "Attribute.2", "Attribute.3", "Attribute.4", "Attribute.5"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}, {"Attribute.3", type text}, {"Attribute.4", type text}, {"Attribute.5", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute.5", "Attribute.1", "Attribute.2"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns"," Period","",Replacer.ReplaceText,{"Attribute.3"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," Value","",Replacer.ReplaceText,{"Attribute.4"}),
    #"Trimmed Text" = Table.TransformColumns(#"Replaced Value1",{{"Attribute.4", Text.Trim, type text}, {"Attribute.3", Text.Trim, type text}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Trimmed Text",{"Value", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Attribute.3", "Attribute.4"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Reordered Columns1", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"Field1", type text}, {"Field2", type text}, {"Field3", type text}, {"Field4", type text}, {"Field5", type text}, {"Field6", type text}, {"Field7", type text}, {"Field8", type text}, {"Field9", type text}, {"Field10", type text}, {"Field11", type text}, {"Field12", type text}, {"Field13", type text}, {"Field14", type text}, {"Field15", type text}, {"Field16", type text}, {"Field17", type text}, {"Field18", type text}, {"Field19", type text}, {"Field20", type text}, {"Field21", type text}, {"2018", Int64.Type}, {"Jan", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"2018", "Year"}, {"Jan", "Period"}})
in
    #"Renamed Columns"