Need help converting Excel file to power bi

[vitiv]

Hello! Is it possible in power query to convert data for PowerBI, which is in the form as in a file? We need a column with product, company, receipt, amount and quantity. (I do not know how to insert the Excel file here)

[v-jingzhang · Accepted solution]

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.

[TheoC]

@v-jingzhang Love your work mate! Keep it up! 🙂

[v-jingzhang]

Thank you😊

[amitchandak]

@vitiv , This is not the ideal format for power bi. Make it like row-column table that will be more suitable

[vitiv]

I agree with you that the format is unsuccessful. However, the sales accounting program issues a report only in this form. I have to format the data for Power BI manually.

[TheoC]

@vitiv once you use the coding provided by @v-jingzhang , you can reuse it as frequently as you need and then create a table in PBI Desktop's canvas that replicates your data as you need it.  The first time takes a couple of mins. After that, it's there forever and you simply hit refresh each time you generate a new annual report.  Many programs have terrible report generation functionality and the whole purpose of Power Query is to give you the power to prepare data as you need it (i.e. clean, manipulate, transform).  This is fundamentally why non-data scientists love Power BI!