# Tags **Software** : [[02 - Tags/Software/Power Query]] **Functions** used : [[Table.AddColumn]] [[Text.Combine]] [[List.Transform]] [[List.RemoveNulls]] [[Record.ToList]] [[Text.From]] [[Table.SplitColumn]] [[Table.PromoteHeaders]] [[Table.SelectRows]] [[Excel.Workbook]] **Date** : 16-March-2025 # Description You'll encounter Jagged datasets especially when uploading data from pdf's in this video i show you how you can use a record and convert it to a list to stack the jagged datasets into one organized dataset. # Video Link <iframe width="560" height="315" src="https://www.youtube.com/embed/HCNvWWoOSno?si=cS5dPJD0C54jp365" 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_Jagged_Stacked_Video.xlsx) [Source File Link GITHUB](https://github.com/jbotes/powerbiTutorials/blob/main/PQ_Jagged_Tables_Source.xlsx) # M-Query ```mquery let Source = Excel.Workbook(File.Contents("C:\Source\PQ_Jagged_Tables_Source.xlsx"), null, true), Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data], #"Added Custom" = Table.AddColumn(Sheet2_Sheet, "Custom", each Text.Combine( List.Transform( List.RemoveNulls(Record.ToList(_)), Text.From), "|") )[[Custom]], #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4"}), #"Changed Type" = Table.PromoteHeaders(Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type text}, {"Custom.3", type text}, {"Custom.4", type text}})), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] <> null and [Date] <> "") in #"Filtered Rows" ``` ## Optional Tags **Tags** : **Technique** : [[Records to Decompose Problem]]