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

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