CODEWITHSUNDEEP
excelmergepowerquerytranspose
Agnew
Agnew

Reputation: 15

Merge rows before executing transpose in Power Query?

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

Upvotes: 1

Views: 434

Answers (1)

horseyride
horseyride

Reputation: 21428

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"

enter image description here

Upvotes: 1

Related Questions