Forum Discussion
BI Power Query Editor
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
Thank you! This helps tremendously. I have one more part I just realized I needed to include, the asset name (which is very important!). The asset is not in any of the rows previously talked about. Reference attachment.
I know from the logic standpoint, I need to keep track of two index values (the row with the site, bldg, dept, locn information and the row with the asset information). From there, I tried creating a new column called "Added Asset" but didn't know the syntax to add the asset content to the new column. Lastly, the asset name is not always a certain number of rows after the rest of the information. This should be the last of my questions for this thread. Thank you in advance.
