Forum Discussion

dc_1820's avatar
dc_1820
Frequent Visitor
6 years ago
Solved

Convert first few rows into new columns to flatten the data

 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👍🏼

Need Help
Tips and Tricks
  • ziying35's avatar
    ziying35
    6 years ago

    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

8 Replies

  • ziying35's avatar
    ziying35
    Icon for Impactful Individual rankImpactful Individual
    6 years ago

    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's avatar
        camargos88
        Icon for Community Champion rankCommunity Champion
        6 years ago

        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"