Merge rows before executing transpose in Power Query?

Question

I need to get these project description rows merged into a single row so that there will be consistency in the number of a rows per record so that I can transpose them into proper columns through Power Query. (see image) I understand how to execute a transpose w/ Power Query if the number of rows are consistent across records but I cannot figure out how to do this if the number of rows differ. The data comes from a PDF which is horribly formatted and breaks the Project Description information in to separate rows. < THAT IS THE KEY PROBLEM. Apart from that the rest is cake. See snippet to see what I mean.

Each transposed record will have seven columns: Director Analysis Address Project Area Notice Date Project Description Appeal

I can get everything I need including the headers. I just can't figure out how to merge the rows under Project Description so that I can proceed w/ the transpose. here is the link to view a screenshot of my sheet

horseyride · Accepted Answer · 2022-03-24 21:00:33Z

1

This is a kludge but seems to work. Assumes the column we want to operate on is named column a in powerquery

It looks for anything between the rows that contain Project Description and Appeals must be

Create a shifted row, so we can see what is on the row above

Add index

Use custom columns to determine which rows need filtering out, and which rows are the start and end rows to combine based on the first column and the shifted first column

Merge text together based on that info, merge that back into original table, then remove the extra rows

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
// create shifted row
    shiftedList = {null} & List.RemoveLastN(Source[a],1),
    custom3 = Table.ToColumns(Source) & {shiftedList},
    custom4 = Table.FromColumns(custom3,Table.ColumnNames(Source) & {"Next Row Header"}),
#"Added Index" = Table.AddIndexColumn(custom4, "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each try if Text.Contains([Next Row Header],"Project Description" ) then [Index] else if     Text.Contains([a],"Appeals must be") then [Index] else null otherwise 0),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
#"Added Custom1" = Table.AddColumn(#"Filled Down", "Custom.1", each try if Text.Contains([Next Row Header],"Project Desc") then "remove" else if     Text.Contains([a],"Appeals must be") then "keep" else null otherwise "keep"),
#"Filled Down1" = Table.FillDown(#"Added Custom1",{"Custom.1"}),
#"Filtered Rows1" = Table.SelectRows(#"Filled Down1", each ([Custom.1] = "remove")),
#"Grouped Rows1" = Table.Group(#"Filtered Rows1", {"Custom"}, {{"Count", each Text.Combine(List.Transform([a], Text.From), ","), type text}}),
#"Merged Queries" = Table.NestedJoin(#"Filled Down1", {"Index"}, #"Grouped Rows1", {"Custom"}, "Table2", JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Count"}, {"Count"}),
#"SwapValue"= Table.ReplaceValue( #"Expanded Table2", each [Custom.1], each if [Count] = null then [Custom.1] else "keep", Replacer.ReplaceValue,{"Custom.1"}),
#"Final Swap"=Table.ReplaceValue(#"SwapValue",each [a], each if [Count]=null then [a] else [Count] , Replacer.ReplaceValue,{"a"}),
#"Filtered Rows" = Table.SelectRows(#"Final Swap", each ([Custom.1] = "keep")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Next Row Header", "Index", "Custom", "Custom.1", "Count"})
in #"Removed Columns"

edited Mar 24, 2022 at 21:00

answered Mar 10, 2022 at 20:17

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Agnew Over a year ago

Whew! that looks complex. Trying to figure it out right now!...

Agnew Over a year ago

works great for single page pdf import - thank you! one thing, when I append tables from multiple pages of the same pdf file the FIRST program description text is in the incorrect location. It gets placed just below the Director's Analysis row as opposed to being just below the Program Description row. All additional page imports have the exact same format as the original. Apart from that all the others are perfect. And honestly, moving that single row manually is not a big deal - this procedure has been super helpful. However, if you have any ideas on the issue I'd love to hear it. Thanks!

Collectives™ on Stack Overflow

Merge rows before executing transpose in Power Query?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related