Transformations help required in power query

Hi PBI_,

Before

After

use this as a new step and replace #"Your Table" with your previous step name

= Table.PromoteHeaders(Table.Skip(#"Your Table", each not List.Contains(Record.ToList(_), "Project ID")))

Whole sample code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZFRb4IwFIX/SkP2KKSlFOHRwZaYJcsy9Mn4UOsNdEJrSnHz36+o02nGTPpy7j3fzT29i4U3VTstBSDVNSsw3siLUpwmjESYOHH7lqMLseYWXI2SgAQhDqMBe96drSSg/zkfZV1LVaJp3o+NWerHNB37JEriOwQXQnfKHkmMx4yFPg1ZGuc+m9AJSwf4rDMGlNg7PS/yOyYEX6LiqgRkTnEGgJm2vEa8Oay07nrrQzxKaBLQIeQ2iOJNj/2Z783oDxBnz4+86lUSDDei6pMVYHaHe0ErjNxaqdWv6gErXuZX7X6lYsNrvqoBzQt02mPgc1FhDfCGbrVwjnZYnCZaEFUgdONKr2A/tdkcZz8xzHwS48hnyXNy6aKpsuAWKN0VAGXglEGZVsollTtp92gGrW3Re6dab7n8Bg==", 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, Column6 = _t, Column7 = _t, Column8 = _t]),
    ReplacedBlank = Table.TransformColumns(Source, {}, each {_, null}{Byte.From(_ = "")}),
    RemovedTopRows = Table.PromoteHeaders(Table.Skip(ReplacedBlank, each not List.Contains(Record.ToList(_), "Project ID")))
in
    RemovedTopRows

o tackle this challenge in Power BI, you can first combine all the CSV files from the folder into a single table. Then, use Power Query transformations to split the 8 rows that act as headers into separate columns. You can do this by:

Importing the files using Folder.Contents.
Merging all files into one table.
Use Table.PromoteHeaders to convert the first 8 rows into headers, or manually extract each row and convert it into column names.
Apply the necessary transformations to clean and organize the data for all files.
This way, you'll handle all the files together and structure your data properly.