Convert first few rows into new columns to flatten the data

[dc_1820]

 Hi Community, I get reports for each departments month wise (2 reports per dept per month) in the following structure:

As Power BI needs data in flat-file format, I would have to convert the data into:

> I need to take the folder where the reports are present (new reports in excel get added regularly)

> Combine & Transform all of the reports to be visualized into a Power BI report/dashboard

Transformation:

> I think the stpes can be applied on the 'Transform Sample File' and the same will be applied for every sheet and new sheet added into the folder.

> Need to take the first 5 rows and make them as columns next to the main data.

> I have tried using transpose, pivot, unpivot in the Power Query editor of Power BI, followed couple of videos and community solutions, but was not able to get the required solution. Can you please help me out with the detailed steps to achieve the same.

Data 

Thanks much in advance👍🏼

[ziying35 · Accepted solution]

Hi, @dc_1820 

let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("tdJNa8MwDAbgv2J8bsF2mn7dsuYSRruQHccOplFHWGIHRymUsf++tN2ovMNGm/RmyfC8Qujlg69s2VZG8iWPodYOKzDIR99t1bVTZ/N2iyyGPZS29v4DvjRtWf6UE78Mz+Xn6FcKsiT2IqJYKvGU9mbX2ryB8+mHVS9WIzC7YxnU1vl7EXIsZmMllOgTkIIrbP5HRHiKYGiZDM9Vjzj6r/zyhtkfs8i/FFsYbOh4/Bk1tpdWp3av6nhDl2Zn8wwq7d4b7gckCJWkEQG1E7OH5nSvbAOQQ86vGf5oK2pPh7UDaqth7Qm1F96+NRbNTm/RusPVbEhZKQZzp3dyZ9QNh13xnNrzwUZe3PEqpKC4t+ZN9J/2+gU=",BinaryEncoding.Base64),Compression.Deflate))),
    firstn = Table.FirstN(Source[[Column1],[Column2]],5),
    pb_rec = Record.FromList(firstn[Column1],firstn[Column2]),
    main_tbl = Table.PromoteHeaders(Table.RemoveFirstN(Source,6)),
    recs = Table.TransformRows(main_tbl,each pb_rec&_),
    result = Table.FromRecords(recs)
in
    result

If my code solves your problem, mark it as a solution

[camargos88]

Hi @dc_1820 ,

Can you post this data here ? So we can copy it.

[dc_1820]

Here is the link to the data - Data 

[camargos88]

Hi @dc_1820 ,

You can replicate this m code, if you are using folder connection use it on Transformation Sample File:

let
    Source = Excel.Workbook(File.Contents("D:\Downloads\Data from Dept.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Department", type text}, {"Product Development", type any}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
    Result = Table.Combine({Table.PromoteHeaders(Table.Skip(#"Changed Type", 5)), Table.PromoteHeaders(Table.FirstN(Table.Transpose(Table.FirstN(#"Changed Type", 4)),2))}),
    #"Filled Up" = Table.FillUp(Result,{"Dept ID","Dept Manager", "Date of Report", "Period of Report"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([KRA] <> null)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"KRA", type text}, {"Points", Int64.Type}, {"Status", type text}, {"Comments", type text}, {"Remarks", type text}, {"Dept ID", type text}, {"Dept Manager", type text}, {"Date of Report", type date}, {"Period of Report", type text}})
in
    #"Changed Type1"

[dc_1820]

@camargos88 Thank you. But the end result is missing the Department Column Can you please explain me what is happening at the Result step. I am able to pretty ,uch understand the reamaining. I want to try this using GUI once so that I easily replicate it. Thank You.

[camargos88]

Hi @dc_1820 ,

Try this one, I missed that column.

let
    Source = Excel.Workbook(File.Contents("D:\Downloads\Data from Dept.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Department", type text}, {"Product Development", type any}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
    Result = Table.Combine({Table.PromoteHeaders(Table.Skip(#"Changed Type", 5)), Table.PromoteHeaders(Table.FirstN(Table.Transpose(Table.DemoteHeaders(Table.FirstN(#"Changed Type", 4))),2))}),
    #"Filled Up" = Table.FillUp(Result,{"Dept ID","Dept Manager", "Date of Report", "Period of Report", "Department"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([KRA] <> null)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"KRA", type text}, {"Points", Int64.Type}, {"Status", type text}, {"Comments", type text}, {"Remarks", type text}, {"Department", type text}, {"Dept ID", type text}, {"Dept Manager", type text}, {"Date of Report", type date}, {"Period of Report", type text}})
in
    #"Changed Type1"

[dc_1820]

@camargos88 @ziying35 @Anonymous Thank you all for the responses. I have accepted @ziying35 response as solution as it was easy to understand to my knowledge.

[Anonymous]

you could get your final table only using GUI:

1) Select TOP part and transpose and add zero-based index:

1) Select BOTTOM part and add zero-based index:

3) then merge on index TOP and BOTTOM and fill-down: