Multiple Headers in Many Rows

[Jose_Amado]

I have a headers in many rows, like to this image, How i can use this with headers in my database ?

[PijushRoy]

Hi @Jose_Amado 

Seems data structure issue, you can try this approach
In Power Query, Select all column and Fill UP value


Home -> Remove Rows -> Remove Top Rows -> Enter 5 -> OK
Then click on "USE FIRST ROWS AS HEADER"
I think now you can set your header
Where is missing values or header shows as values, you need to filter out null value and replace value to correct the data

If solve your requirement, please mark this answer as SOLUTION
If this comment helps you, please LIKE this comment/Kudos

his steps you can follow

[Jose_Amado]

Thanks for helping. But this method is not effectiveness in this, because, how can you see, the headers are in differents row, and, if i use Fill down or fill up, any columns will been wrong. My situations is... Exist a method that i can use to aligment in the same rows all headers, see column 6 for example

[v-yingjl · Accepted solution]

Hi @Jose_Amado ,

A bit complex but you can try that:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hY4xC8IwFIT/inQuXF40bTNWK046q6VDpRkK1UDq/8fXNg/qIEJCjuPL3dV1kqSr06Q/jap9u/hsyu7Zj3/4lXHwQ/vwoe18YOvkXi74b6JkceVL1irsYFlqRQQNUlJauXHoZ3rP1m2mCwULI7SRjyxASzUb9wW1KEC5sKSgpb1icV6gAlvoCGlkICOJJiYe2bjI1ClIC53LkmzabZOm+QA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Replaced Value" = Table.ReplaceValue(#"Added Index",each [Column1] ,each if [Index] = 1 then List.Select(#"Added Index"[Column1],each Text.Length(_)>0){0} else [Column1],Replacer.ReplaceValue,{"Column1"}),
    Custom1 = Table.ReplaceValue(#"Replaced Value",each [Column2] ,each if [Index] = 1 then List.Select(#"Added Index"[Column2],each Text.Length(_)>0){0} else [Column2],Replacer.ReplaceValue,{"Column2"}),
    Custom2 = Table.ReplaceValue(#"Custom1",each [Column3] ,each if [Index] = 1 then List.Select(#"Added Index"[Column3],each Text.Length(_)>0){0} else [Column3],Replacer.ReplaceValue,{"Column3"}),
    Custom3 = Table.ReplaceValue(#"Custom2",each [Column4] ,each if [Index] = 1 then List.Select(#"Added Index"[Column4],each Text.Length(_)>0){0} else [Column4],Replacer.ReplaceValue,{"Column4"}),
    Custom4 = Table.ReplaceValue(#"Custom3",each [Column5] ,each if [Index] = 1 then List.Select(#"Added Index"[Column5],each Text.Length(_)>0){0} else [Column5],Replacer.ReplaceValue,{"Column5"}),
    #"Promoted Headers" = Table.PromoteHeaders(Custom4, [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Colaborador", type text}, {"Genero", type text}, {"Date", type text}, {"Date Admis", type text}, {"Date Desli", type text}, {"1", Int64.Type}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Changed Type1",Table.ColumnNames(#"Changed Type1"){0},"",Replacer.ReplaceValue,{"Colaborador"}),
    Custom5 = Table.ReplaceValue(#"Replaced Value1",Table.ColumnNames(#"Changed Type1"){1},"",Replacer.ReplaceValue,{"Genero"}),
    Custom6 = Table.ReplaceValue(#"Custom5",Table.ColumnNames(#"Changed Type1"){2},"",Replacer.ReplaceValue,{"Date"}),
    Custom7 = Table.ReplaceValue(#"Custom6",Table.ColumnNames(#"Changed Type1"){3},"",Replacer.ReplaceValue,{"Date Admis"}),
    Custom8 = Table.ReplaceValue(#"Custom7",Table.ColumnNames(#"Changed Type1"){4},"",Replacer.ReplaceValue,{"Date Desli"}),
    #"Removed Columns" = Table.RemoveColumns(Custom8,{"1"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Colaborador", type text}, {"Genero", type text}, {"Date", type date}, {"Date Admis", type date}, {"Date Desli", type date}})
in
    #"Changed Type2"

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.