Excel Table with Wrapped Text fields

dakturn357

New Member
Joined
Oct 18, 2018
Messages
2
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


130141705/15/20185/15/20184566561WM21CO929725M
needs basework, missing bolts
130141703/6/20183/6/20184566562WM21CO929765M
ALSO NEED TO CHECK FOR RUB ON GUARD AND INSPECT BEARINGS DUE TO FAILED SEALS
130141705/29/20185/29/20184574513WM21CO929305M
130141706/1/20186/1/20184576338WM21CO929530M
PROBLEM WHERE AGITATOR TANK SHAFT GOES THROUGH TOP OF TANK. NEED TO LOOK INTO DIFFERENT DESIGN TO STOP BLACK LEAKS.
130141707/9/20187/9/20184599111WM21CO929810M

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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"

Code:
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"
 
Last edited:
Upvote 0
Come to think of it, this is a bit easier and simpler. Same set up

Code:
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"
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top