Forum Discussion
BI Power Query Editor
Hi Rachel
If you don't need to keep <other info> rows when Fill Up and Keep Top rows (1)
As attached
How do you accomplish this?
Hi Rachel,
That's in Power Query editor ribbon
and
Thank you. I am also looking into potentially combining text files before importing them in to Excel. This would be what it would look like initially...
Description Site Building Dept Location
<description> null null null null
other info <site> <bldg> <dept> <locn>
other info null null null null
<description> null null null null
other info <site> <bldg> <dept> <locn>
other info null null null null
And this is what the end goal is:
Description Site Building Dept Location
<description> <site> <bldg> <dept> <locn>
<description2> <site2> <bldg2> <dept2> <locn2>
i'm not sure if this is feasible or not with Excel
You may combine all your text files by Power Query from excel, e.g. by using From Folder connector. If your combined files looks like in your example, i.e. Site..Location columns are always empty in all rows but one for each description; and Description is always in the row before to non-empty Site..Location, when you may
- add Index column with index starts from 0;
- add custom column for which if Site <> null you take Description from previous row;
- filter the result and add some cosmetic
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1), AddDescription = Table.AddColumn(AddIndex, "AddDescription", each if [Site] <> null then AddIndex{[Index]-1}[Description] else null), RemoveUnused = Table.SelectColumns(AddDescription,{"AddDescription", "Site", "Building", "Dept", "Location"}), FilterEmpty = Table.SelectRows(RemoveUnused, each ([AddDescription] <> null)), OldColumnName = Table.RenameColumns(FilterEmpty,{{"AddDescription", "Description"}}) in OldColumnNameand attached