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 we...

Need Help

v-chenwuz-msft

4 years ago

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: