# Tags **Software** : [[02 - Tags/Software/Power Query]] **Functions** used : [[Folder.Files]] [[Table.ExpandTableColumn]] [[Table.AddColumn]] [[List.ContainsAny [[Record.ToList]]]] [[Table.Skip]] [[Table.TransformColumns]] [[Table.PromoteHeaders]] [[Table.Combine]] **Date** : 29-March-2025 # Description 🌟 In today's tutorial, we're diving deep into the magic of Power Query to help you declutter your data. Tired of manually removing junk rows from multiple files? 🗑️ Fed up with combining them one by one? 😤 Look no further! 🔍 What you'll learn in this video: 1️⃣ How to identify and remove junk rows from individual files in a folder. 🚫📜 2️⃣ The steps to dynamically combine these cleaned-up files into one master file. 🔄📁 3️⃣ Tips and tricks to ensure your data remains consistent and error-free. 💡 Whether you're a beginner or an Excel pro, this tutorial has something for everyone! So, grab a cup of coffee ☕, sit back, and let's transform your data game! 💪 # Video Link <iframe width="560" height="315" src="https://www.youtube.com/embed/kXBGWqE3TeQ?si=9hjxraVSjpQ6g5wO" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen></iframe> **Solution File All the Power Query code is in this one [GITHUB File Link](https://github.com/jbotes/powerbiTutorials/blob/main/PQ_JunkRows_Video.xlsx) **Source Files** Source Files used in the solution file [File 1](https://github.com/jbotes/powerbiTutorials/blob/main/01_ValueInvestorHoldings31122021.xlsx) [File 2](https://github.com/jbotes/powerbiTutorials/blob/main/05_ValueInvestorHoldings31122022_2.xlsx) [File 3](https://github.com/jbotes/powerbiTutorials/blob/main/06_ValueInvestorHoldings31032023.xlsx) # M-Query ```mquery let Source = Folder.Files("C:\PBI_Source_Demos\BAsenseiFolder"), #"C:\PBI_Source_Demos\BAsenseiFolder\_05_ValueInvestorHoldings31122022_2 xlsx" = Source{[#"Folder Path"="C:\PBI_Source_Demos\BAsenseiFolder\",Name="05_ValueInvestorHoldings31122022_2.xlsx"]}[Content], #"Imported Excel Workbook" = Excel.Workbook(#"C:\PBI_Source_Demos\BAsenseiFolder\_05_ValueInvestorHoldings31122022_2 xlsx"), #"Expanded Data" = Table.ExpandTableColumn(#"Imported Excel Workbook", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19"}), #"Added Custom" = Table.AddColumn(#"Expanded Data", "Custom", each List.ContainsAny(Record.ToList(_), {"Shares", "Value", "RecentActivity"})), Custom1 = Table.Skip(#"Added Custom", each not List.ContainsAny(Record.ToList(_), {"Shares", "Value", "RecentActivity"})) in Custom1 ``` ```mquery let Source = Folder.Files("C:\PBI_Source_Demos\BAsenseiFolder"), Custom1 = Table.TransformColumns(Source, {"Content", Excel.Workbook}), #"Expanded Content" = Table.ExpandTableColumn(Custom1, "Content", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"}), #"Added Custom" = Table.AddColumn(#"Expanded Content", "Custom", each Table.PromoteHeaders(Table.Skip([Data], each not List.ContainsAny(Record.ToList(_), {"Shares", "Value", "RecentActivity"})))), Custom2 = Table.Combine(#"Added Custom"[Custom]) in Custom2 ``` ## Optional Tags **Tags** : **Technique** : [[Working with lists]] [[Records to Decompose Problem]]