# Tags **Software** : [[02 - Tags/Software/Power Query]] **Functions** used : [[Excel.Workbook]] [[Table.AddIndexColumn]] [[Table.SelectRows]] [[Table.SplitAt]] [[List.Transform]] [[Table.RemoveColumns]] [[Table.PromoteHeaders]] [[Table.UnpivotOtherColumns]] [[Table.Combine]] [[Table.TransformColumnTypes]] [[Table.Pivot]] **Date** : 21-March-2025 # Description 🚀 Explore the power of Power Query with our latest tutorial! Learn how to dynamically split your dataset into two using the powerful Table.SplitAt function. 📊 Watch as we guide you through the process of reconstructing and pivoting your tables seamlessly using nested Let statements. 💡 Level up your data transformation game with this step-by-step tutorial! 🔧 # Video Link <iframe width="560" height="315" src="https://www.youtube.com/embed/FB5Zs29A8fw?si=cU-SHPViheJNG-1v" 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_DynamicSplit_Video.xlsx) [Source File GIT HUB Link](https://github.com/jbotes/powerbiTutorials/blob/main/Pq_SplitDynamically_Source.xlsx) # M-Query ```mquery let Source = Excel.Workbook(File.Contents("C:\PQ - Split Dynamically\Pq_SplitDynamically_Source.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Added Index" = Table.AddIndexColumn(Sheet1_Sheet, "Index", 1, 1, Int64.Type), SplitLocation = Table.SelectRows(#"Added Index", each [Column1] = null)[Index]{0}, Custom1 = Table.SplitAt(#"Added Index", SplitLocation), Custom2 = List.Transform(Custom1, each let a = Table.RemoveColumns(_, "Index"), b = Table.PromoteHeaders(a), c = Table.UnpivotOtherColumns(b, {"Stock"}, "Period Name", "Amount") in c ), Custom3 = Table.Combine(Custom2), #"Changed Type" = Table.TransformColumnTypes(Custom3,{{"Amount", Int64.Type}}), #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[#"Period Name"]), "Period Name", "Amount", List.Sum) in #"Pivoted Column" ``` ## Optional Tags **Tags** : **Technique** : [[Nested Let]]