Forum Discussion
PowerQuery - Semi structured data into table
Hello,
I have a csv file that is semi-structured. I need to be able to extract the information I want and respresent in a tabular manner. I've tried fill from example and it's not working out as well as I want it to.
This is what the source file looks like
This is what the final output should look like
I want to convert the source file without headers into a table that looks like the second table.
I've tried wrangling it with powerquery a few times, unfortunately I can't seem to figure out how to seperate the top few rows from the bottom and the two different types of statistics captured. Any help will be greatly appreciated.
Here's the link to the source file sourcefile
Hi chat_peters ,
Please try this M code:
let Source = Csv.Document(File.Contents("C:\Users\Admin\Desktop\data.csv"),[Delimiter=",", Columns=9, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}, {"Column9", Int64.Type}}), #"Removed Top Rows" = Table.Skip(#"Changed Type",10), #"Added Custom" = Table.AddColumn(#"Removed Top Rows", "blank", each null), #"Transposed Table" = Table.Transpose(#"Added Custom"), #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1", "Column9"}), #"Transposed Table1" = Table.Transpose(#"Filled Down"), #"Filled Down1" = Table.FillDown(#"Transposed Table1",{"Column10"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down1", each [Column1] <> "" and [Column1] <> "Result Type:"), #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Week", type text}, {"Person", type text}, {"1", Int64.Type}, {"2", Int64.Type}, {"3", Int64.Type}, {"4", Int64.Type}, {"5", Int64.Type}, {"6", Int64.Type}, {"7", Int64.Type}, {"Average", type text}}), #"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each ([Week] <> "Week")), #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"Average", "Type"}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Week", "Person", "Type"}, "Attribute", "Value"), #"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Day of week"}}), // date #"Kept First Rows" = Table.FirstN(#"Changed Type",3), #"Transposed Table_date" = Table.Transpose(#"Kept First Rows"), // Time in, Time out, Date #"Timein" = #"Transposed Table_date"{1}[Column1], #"Timeout" = #"Transposed Table_date"{1}[Column2], #"Date" = #"Transposed Table_date"{1}[Column3], // Add column #"Added column" = Table.AddColumn(#"Renamed Columns1","Time in", each #"Timein"), #"Added Custom1" = Table.AddColumn(#"Added column", "Time out", each #"Timeout"), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Date", each #"Date"), #"Reordered Columns" = Table.ReorderColumns(#"Added Custom2",{"Date", "Time in", "Time out", "Week", "Person", "Type", "Day of week", "Value"}), #"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns",{{"Date", type date}, {"Time in", type datetime}, {"Time out", type datetime}}) in #"Changed Type2"Result:
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi chat_peters ,
Please try this M code:
let Source = Csv.Document(File.Contents("C:\Users\Admin\Desktop\data.csv"),[Delimiter=",", Columns=9, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}, {"Column9", Int64.Type}}), #"Removed Top Rows" = Table.Skip(#"Changed Type",10), #"Added Custom" = Table.AddColumn(#"Removed Top Rows", "blank", each null), #"Transposed Table" = Table.Transpose(#"Added Custom"), #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1", "Column9"}), #"Transposed Table1" = Table.Transpose(#"Filled Down"), #"Filled Down1" = Table.FillDown(#"Transposed Table1",{"Column10"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down1", each [Column1] <> "" and [Column1] <> "Result Type:"), #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Week", type text}, {"Person", type text}, {"1", Int64.Type}, {"2", Int64.Type}, {"3", Int64.Type}, {"4", Int64.Type}, {"5", Int64.Type}, {"6", Int64.Type}, {"7", Int64.Type}, {"Average", type text}}), #"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each ([Week] <> "Week")), #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"Average", "Type"}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Week", "Person", "Type"}, "Attribute", "Value"), #"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Day of week"}}), // date #"Kept First Rows" = Table.FirstN(#"Changed Type",3), #"Transposed Table_date" = Table.Transpose(#"Kept First Rows"), // Time in, Time out, Date #"Timein" = #"Transposed Table_date"{1}[Column1], #"Timeout" = #"Transposed Table_date"{1}[Column2], #"Date" = #"Transposed Table_date"{1}[Column3], // Add column #"Added column" = Table.AddColumn(#"Renamed Columns1","Time in", each #"Timein"), #"Added Custom1" = Table.AddColumn(#"Added column", "Time out", each #"Timeout"), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Date", each #"Date"), #"Reordered Columns" = Table.ReorderColumns(#"Added Custom2",{"Date", "Time in", "Time out", "Week", "Person", "Type", "Day of week", "Value"}), #"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns",{{"Date", type date}, {"Time in", type datetime}, {"Time out", type datetime}}) in #"Changed Type2"Result:
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much for the help on this. I started by subsetting data the original dataset but looking at your code, I can skip several of the steps I was doing. This is a way better approach with fewer steps than what I had started. Thank you a million times 🙂
I need similar kind of help. I need to be able to separate one column of data into several columns where a certain delimiter appears.
I want to break this column into several columns by : delimiter. I want to create a new column everywhere : delimiter appears. I know how to find the position of the delimiter I just don't know how to loop through each instance. given below is the end result I hope to acheive.
