transforming data in M

[g_reep]

So currently I have data that is in this format 

Heading 1
Column 1	Column 2	Column 3	Column 4
Data_Row	Data_Row	Data_Row	Data_Row
Data_Row	Data_Row	Data_Row	Data_Row
Heading 2
Column 1	Column 2	Column 3	Column 4
Data_Row	Data_Row	Data_Row	Data_Row
Data_Row	Data_Row	Data_Row	Data_Row
Data_Row	Data_Row	Data_Row	Data_Row
Data_Row	Data_Row	Data_Row	Data_Row
Data_Row	Data_Row	Data_Row	Data_Row
Heading 3
Column 1	Column 2	Column 3	Column 4
Data_Row	Data_Row	Data_Row	Data_Row
Data_Row	Data_Row	Data_Row	Data_Row
Data_Row	Data_Row	Data_Row	Data_Row
Data_Row	Data_Row	Data_Row	Data_Row
Data_Row	Data_Row	Data_Row	Data_Row

However I want to transform it into data like this

Column 1	Column 2	Column 3	Column 4	Column 5
Data_Row	Data_Row	Data_Row	Data_Row	Heading 1
Data_Row	Data_Row	Data_Row	Data_Row	Heading 1
Data_Row	Data_Row	Data_Row	Data_Row	Heading 2
Data_Row	Data_Row	Data_Row	Data_Row	Heading 2
Data_Row	Data_Row	Data_Row	Data_Row	Heading 2
Data_Row	Data_Row	Data_Row	Data_Row	Heading 2
Data_Row	Data_Row	Data_Row	Data_Row	Heading 2
Data_Row	Data_Row	Data_Row	Data_Row	Heading 3
Data_Row	Data_Row	Data_Row	Data_Row	Heading 3
Data_Row	Data_Row	Data_Row	Data_Row	Heading 3
Data_Row	Data_Row	Data_Row	Data_Row	Heading 3
Data_Row	Data_Row	Data_Row	Data_Row	Heading 3

is that possible to do in DAX or M?

[Ashish_Mathur · Accepted solution]

Hi,

On your sample data, this M code works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8khNTMnMS1cwVNJRgqJYnWgl5/yc0tw8sCiUaYRgGiOYJmDVLoklifFB+eVAcQJM0lWjugvmXKPB6tzBoxp7wBmPBhxe1bEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Column1", "Column2", "Column3", "Column4"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each if [Column2]=null then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column2] <> null)),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column 1", type text}, {"Column 2", type text}, {"Column 3", type text}, {"Column 4", type text}, {"Heading 1", type text}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each ([Column 1] = "Data_Row")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"Heading 1", "Column 5"}})
in
    #"Renamed Columns"

Hope this helps.