Excel Table with Wrapped Text fields

[dakturn357]

I have an excel file that contains an extract from a database. The rows come out messed up. There is a free text field in some of the rows that gets put on a second empty row (like word wrap). If the free text area has a small size, it will stay in the same row as the main data. When I bring these values into Power BI, there are null values in all of the blanks. I've tried to split the data, but the rows with the wrapped text fields are not even, they are random. We print the report to CSV, but it has PDF in the main header when you export, so I am thinking the data gets converted to different formats several times.


I need to roll the free text area into the line it belongs in, but I can't figure out how to do it. There are over 1000 rows of data and these database extracts happen every few days. If any one has an idea or trick, that would be awesome.

Thanks,
Derek


[TABLE="width: 2266"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: right"]13014170[/TD]
[TD="align: right"]5/15/2018[/TD]
[TD="align: right"]5/15/2018[/TD]
[TD="align: right"]4566561[/TD]
[TD]WM[/TD]
[TD="align: right"]21[/TD]
[TD]CO929725M[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]needs basework, missing bolts[/TD]
[/TR]
[TR]
[TD="align: right"]13014170[/TD]
[TD="align: right"]3/6/2018[/TD]
[TD="align: right"]3/6/2018[/TD]
[TD="align: right"]4566562[/TD]
[TD]WM[/TD]
[TD="align: right"]21[/TD]
[TD]CO929765M[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ALSO NEED TO CHECK FOR RUB ON GUARD AND INSPECT BEARINGS DUE TO FAILED SEALS[/TD]
[/TR]
[TR]
[TD="align: right"]13014170[/TD]
[TD="align: right"]5/29/2018[/TD]
[TD="align: right"]5/29/2018[/TD]
[TD="align: right"]4574513[/TD]
[TD]WM[/TD]
[TD="align: right"]21[/TD]
[TD]CO929305M[/TD]
[/TR]
[TR]
[TD="align: right"]13014170[/TD]
[TD="align: right"]6/1/2018[/TD]
[TD="align: right"]6/1/2018[/TD]
[TD="align: right"]4576338[/TD]
[TD]WM[/TD]
[TD="align: right"]21[/TD]
[TD]CO929530M[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PROBLEM WHERE AGITATOR TANK SHAFT GOES THROUGH TOP OF TANK. NEED TO LOOK INTO DIFFERENT DESIGN TO STOP BLACK LEAKS.[/TD]
[/TR]
[TR]
[TD="align: right"]13014170[/TD]
[TD="align: right"]7/9/2018[/TD]
[TD="align: right"]7/9/2018[/TD]
[TD="align: right"]4599111[/TD]
[TD]WM[/TD]
[TD="align: right"]21[/TD]
[TD]CO929810M[/TD]
[/TR]
</tbody>[/TABLE]

 

[horseyride]

Powerquery M language version would be something along these lines
Assumes named range Table1 has seven columns of data with no headers, and that the trigger for a 2nd row in seventh column is a null / blank value in Column6
Output moves the extra row into a new column called "Extra"

let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Duplicated Column" = Table.DuplicateColumn(Source, "Column6", "Column6 - Copy"),
    #"Filled Down" = Table.FillDown(#"Duplicated Column",{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}),
    NonNullRows = Table.SelectRows(#"Filled Down", each ([#"Column6 - Copy"] <> null)),
    NullRows = Table.SelectRows(#"Filled Down", each ([#"Column6 - Copy"] = null)),
    #"Merged Queries" = Table.NestedJoin(NonNullRows,{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"},NullRows ,{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"},"NullRows ",JoinKind.LeftOuter),
    #"Expanded NullRows " = Table.ExpandTableColumn(#"Merged Queries", "NullRows ", {"Column7"}, {"Extra"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded NullRows ",{"Column6 - Copy"})
in  #"Removed Columns"

 

[horseyride]

Come to think of it, this is a bit easier and simpler. Same set up

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if #"Added Index"{[Index]+1}[Column6] = null then #"Added Index"{[Index]+1}[Column7]  else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Column6] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"})
in #"Removed Columns"

 

[dakturn357]

Thanks horseyride, I'll give it a shot. I just purchased a book on the M language. In the mean time, I figured out how to do this in Excel using a formula to assign a key to the like rows, duplicating the worksheet, importing the two tables into PowerQuery and using the Related function. Takes less than 10 min this way, but the goal is to do it in Power BI as the data gets imported.