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 conver...
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

dc_1820's avatar
dc_1820
Frequent Visitor
6 years ago

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

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's avatar
    dc_1820
    Frequent Visitor
    6 years ago

    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.