Forum Discussion
Transformations help required in power query
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.
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 RemovedTopRowso 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.
5 Replies
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 RemovedTopRowso 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.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.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.
