Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
vitiv
New Member

Need help converting Excel file to power bi

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)Sales_Test.jpg

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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. 

21093001.jpg

 

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

21093002.jpg

 

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.

View solution in original post

6 REPLIES 6
v-jingzhang
Community Support
Community Support

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. 

21093001.jpg

 

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

21093002.jpg

 

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.

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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Thank you😊

amitchandak
Super User
Super User

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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.

@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! 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.