Power Query: Edit Rows, not Columns

[jbeam4me]

Apologies in advance for my PQ ignorance as I'm fairly new to its use. I do, however, feel I've combed the internet in search for an answer to my question, have not found it, and, thus, have landed here

1) I "Power Query" a local unedited excel corporate-provided report and begin my edits.
2) I can remove the first two rows of "unneeded text" without issue.
3) The issue I'm having is figuring out a way to Concatenate (or another method) to get B4:D4 to display a date based on provided text values pre-populated in B3:D4.

In simpler terms: I'm trying to automate my query transformation steps so that upon "refresh", the editor leaves me with MM/YY in B4:D4 (the data I'm working with could include up to 10+ years of columns--whereas below I'm simply listing a simple example for this question).

A1:D6 table example within Power Query Editor
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]unneeded text[/TD]
[TD]null[/TD]
[TD]null[/TD]
[TD]null[/TD]
[/TR]
[TR]
[TD]unneeded text [/TD]
[TD]null[/TD]
[TD]null[/TD]
[TD]null
[/TD]
[/TR]
[TR]
[TD]null[/TD]
[TD]Y2015[/TD]
[TD]Y2015[/TD]
[TD]Y2015[/TD]
[/TR]
[TR]
[TD]null[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[/TR]
[TR]
[TD]Billed Revenue[/TD]
[TD]$1[/TD]
[TD]$1[/TD]
[TD]$1[/TD]
[/TR]
[TR]
[TD]Accrued Revenue[/TD]
[TD]$2[/TD]
[TD]$2[/TD]
[TD]$2[/TD]
[/TR]
</tbody>[/TABLE]











What I hope to achieve (after this step, I would delete the first 3 rows and then make first row as my header row within editor).
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]unneeded text[/TD]
[TD]null[/TD]
[TD]null[/TD]
[TD]null[/TD]
[/TR]
[TR]
[TD]unneeded text [/TD]
[TD]null[/TD]
[TD]null[/TD]
[TD]null
[/TD]
[/TR]
[TR]
[TD]null[/TD]
[TD]Y2015[/TD]
[TD]Y2015[/TD]
[TD]Y2015[/TD]
[/TR]
[TR]
[TD]null[/TD]
[TD]01/15[/TD]
[TD]02/15[/TD]
[TD]03/15[/TD]
[/TR]
[TR]
[TD]Billed Revenue[/TD]
[TD]$1[/TD]
[TD]$1[/TD]
[TD]$1[/TD]
[/TR]
[TR]
[TD]Accrued Revenue[/TD]
[TD]$2[/TD]
[TD]$2[/TD]
[TD]$2

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Matt Allington]

The only way I know how to handle this is to transpose the data (columns become rows), then parse the dates together and then transpose back.

 

[MarcelBeug]

So that would look something like:

let
    Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
    Transposed = Table.Transpose(Source),
    MonthNumbers = Table.TransformColumns(Transposed,{{"Column4", each if List.PositionOf({"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}, _) = -1 then _ else Text.PadStart(Text.From(1+List.PositionOf({"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}, _)),2,"0")}}),
    YearsWithSlash = Table.TransformColumns(MonthNumbers,{{"Column3", each if Text.StartsWith(_,"Y") and Text.Length(_) = 5 then "/"&Text.End(_,2) else _}}),
    Merged = Table.CombineColumns(YearsWithSlash,{"Column4", "Column3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    TransposedBack = Table.Transpose(Merged)
in
    TransposedBack