Transformations help required in power query

[PBI_]

Hi Team,
Looking for assistance with a power query challenge. I have a folder containing ten CSV files with the same format. I uploaded the folder to powerbi. The tough aspect is creating separate columns for 8 rows in the csv file, which resembles a header section. However, another difficult aspect is the requirement to work on all ten csv files in Power Bi. For your reference i have shared you the screenshot how the csv file looks. can any one help on this plzz guys.

[dufoq3 · Accepted solution]

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

[v-karpurapud]

Hello @PBI_ 

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[v-karpurapud]

Hi @PBI_ 

If you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[Omid_Motamedise · Accepted solution]

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.

[v-karpurapud]

Hello @PBI_ 

Could you please confirm if your query have been resolved the solution provided by @dufoq3 ? If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

Thank you

[dufoq3 · Accepted solution]

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