Need help converting Excel file to power bi

Hi vitiv 

 

You need to transform data in Power Query editor to change the format after connecting to this Excel file. 

 

For example, when you connect to Excel file and enter Power Query editor, the table may look like below. 

 

Then you could execute several transformation steps to change it into below table. This would be a good structure for analyzation and visualization. 

 

You could copy below codes into a blank query's advanced editor to see the steps. Or download the pbix file at bottom. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lVBLCoMwEL1KcC0yk5lM4roXKN2KC7EuuvBDsYvevlESKRiEhCzem8z7kKYpirK4zePSTV81dePgabhtuT/e3/Pz06+n+WnwGPrhtawKAD0jMTXpCkB5IlA7V4ndMBpnZZ8HXUzPU4VaSmepLkrvRtr5E4wMO+RoxMZQqnSeKpbGLNVFafKMCfgwsmg5YjT+W1Kl81T/P41knI6bWgtFzL50SrRFYQ3gICwSyoFFXDKJNxGwORZ9PYJTUvsD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, DOC = _t, Sum = _t, Tax = _t, Quantity = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"DOC", type text}, {"Sum", Int64.Type}, {"Tax", type number}, {"Quantity", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"(blank)"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Sum] <> null)),
    #"Added Conditional Column" = Table.AddColumn(#"Filtered Rows", "Receipt", each if Text.Contains([DOC], "Receipt") then [DOC] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Receipt"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Receipt"}, {{"GroupTable", each _, type table [DOC=nullable text, Sum=nullable number, Tax=nullable number, Quantity=nullable number, Receipt=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([GroupTable],"Index",1,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"DOC", "Sum", "Tax", "Quantity", "Receipt", "Index"}, {"DOC", "Sum", "Tax", "Quantity", "Receipt", "Index"}),
    #"Added Conditional Column1" = Table.AddColumn(#"Expanded Custom", "Company", each if [Index] = 2 then [DOC] else null),
    #"Filled Down1" = Table.FillDown(#"Added Conditional Column1",{"Company"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down1", each ([Index] <> 1 and [Index] <> 2)),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"DOC", "Product"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Receipt", "Company", "Product", "Sum", "Tax", "Quantity"})
in
    #"Reordered Columns"

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.