# Tags **Software** : [[02 - Tags/Software/Power Query]] **Functions** used : [[Table.AddColumn]] [[Table.SelectColumns]] [[Table.ExpandTableColumn]] [[Table.TransformColumnTypes]] [[Table.Skip]] [[Table.Transpose]] [[Table.TransformColumns]] [[Splitter.SplitTextByCharacterTransisition]] [[Text.Combine]] [[Table.PromoteHeaders]] [[Table.UnpivotOtherColumns]] [[Excel.Workbook]] **Date** : 19-March-2025 # Description - 🚀 Unlock the Power of Power Query: Dynamic Spaces, Unpivot Magic, and Clean Sheets! 🚀 Welcome to our latest tutorial where we dive deep into the transformative world of Power Query! If you've ever struggled with camel case column names, wished for a seamless way to unpivot datasets, or needed a magic wand to remove those pesky junk rows from various sheets, this video is your new best friend. 🌟 🔍 What You'll Learn: Dynamic Camel Case Solution: Learn how to automatically add spaces to camel case column names, making your data more readable and easier to understand. Say goodbye to squinting! 👀 Unpivot Wizardry: Discover how to unpivot your dataset with ease, transforming your data into a format that's perfect for analysis. It's like seeing your data in 3D! 📊 Junk Row Exorcism: We'll share a powerful function that cleans out unwanted rows from various sheets, leaving you with nothing but the pristine data you need. Clean sheets, clear mind! 🧹 # Video Link <iframe width="560" height="315" src="https://www.youtube.com/embed/B5tkkOcteVs?si=GmvsXfLtB9la_kiA" 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> [GITHUB File Link](https://github.com/jbotes/powerbiTutorials/blob/main/Pq_DynamicTRansformColumnNames_Video.xlsx) # M-Query ```Query 1 let Source = Excel.Workbook(File.Contents("C:\ActualDataset.xlsx"), null, true), #"Added Custom" = Table.AddColumn(Source, "Custom", each ColumnNameFunction([Data])), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Portfolio Date", "Portfolio Name", "Stock", "NrOfStocks"}, {"Portfolio Date", "Portfolio Name", "Stock", "NrOfStocks"}), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Portfolio Date", type date}, {"NrOfStocks", Int64.Type}}) in #"Changed Type" ``` ```Function (inputtable as table) => let Skipped = Table.Skip(inputtable, each [Column1] <> "PortfolioDate"), Headers = Table.Transpose(Table.FirstN(Skipped,1)), FixText = Table.TransformColumns(Headers, {"Column1", each let split = Splitter.SplitTextByCharacterTransition({"a".."z"},{"A".."Z"})(_), combine = Text.Combine(split, " ") in combine }), Promote = Table.PromoteHeaders(Table.Transpose(FixText) & Table.Skip(Skipped)), Custom1 = Table.UnpivotOtherColumns(Promote, {"Portfolio Date", "Portfolio Name"}, "Stock", "NrOfStocks") in Custom1 ``` ## Optional Tags **Tags** : **Technique** : [[Custom Function]]