将一列移到另一列的末尾 [英] Moving one column to the end of another
问题描述
我有一堆excel文件，它们的格式很奇怪，我想为其创建自动导入脚本，因此我可以轻松地以正确的格式将这些数据保存到工作表中.
I have a bunch of excel files that are formatted in a pretty weird way that I would like to make an automated import script for, so I can easily get this data into a sheet with the correct formatting.
在前12列中每个月都有小时值，在接下来的12列中有日期和小时.
It has hourly values for each month in the first 12 columns, then the date along with hours in the next 12 columns.
我想做的就是能够将这些数据保存到一个表中，其中第一列具有日期和小时(以excel格式)，第二列包含该数据.我的想法是在通过功率查询调整数据的过程中记录一个宏，然后在多个文件中重复该宏.但是，我似乎找不到一种很好的方法来使第2列"的数据移动到第1列"的末尾，并使用Power Query对值和日期重复该操作.有指针吗?
What I would like to do is be able to get this data in to a table where the first column has the date and hour (in excel format) and the second one containing the data. My thought was to record a macro during the process of adjusting the data with power query and then repeating the macro with multiple files. However, I can't seem to find a good way to make "Column 2"'s data moved to the end of "Column 1" and repeating that for both values and the dates using Power Query. Any pointers?
还请注意，第1列的长度与第2列的长度不同，因为1月份的值比2月份多.但是，列1s的长度与列13相同，列2s的长度与14相同，依此类推.
Also notice, column 1s length differs from column 2 since January has more values than February. However, column 1s lenght is the same as column 13, column 2s length same as 14 and so on.
我已经在此处上传了示例文件
I have uploaded a sample file here
推荐答案
从头开始创建空白查询(在我的计算机上，我通过以下方式在Excel中执行此操作:Data > Get Data > From Other Sources > Blank Query).
Create a blank query from scratch (on my machine I do this in Excel via: Data > Get Data > From Other Sources > Blank Query).
单击Home > Advanced Editor，复制并粘贴以下代码，然后将此行folderPath = "C:\Users\user\",更改为包含Excel文件的父文件夹的路径.然后单击Close & Load.
Click Home > Advanced Editor, copy-paste the code below and change this line folderPath = "C:\Users\user\", to the path of the parent folder that contains the Excel files. Then click Close & Load.
由于要从多个工作簿(可能还有多个工作表)导入数据，因此，已加载表的前两列应该是该行数据来自的工作簿和工作表. (如果要删除前两列，请编辑查询.)
As the data is being imported from multiple workbooks (and possibly multiple sheets), the first two columns of the loaded table should be the workbook and worksheet that that row of data came from. (If you want to get rid of the first two columns, edit the query.)
let
folderPath = "C:\Users\user\",
getDataFromSheet = (sheetData as table) =>
let
promoteHeaders = Table.PromoteHeaders(sheetData, [PromoteAllScalars=true]),
standardiseHeaders =
let
headers = Table.ColumnNames(promoteHeaders),
zipWithLowercase = List.Zip({headers, List.Transform(headers, Text.Lower)}),
renameAsLowercase = Table.RenameColumns(promoteHeaders, zipWithLowercase)
in
renameAsLowercase,
emptyTable = Table.FromColumns({{},{}}, {"Date", "Value"}),
monthsToLoopOver = {"januari", "februari", "mars", "april", "maj", "juni", "juli", "augusti", "september", "oktober", "november", "december"},
appendEachMonth = List.Accumulate(monthsToLoopOver, emptyTable, (tableState, currentMonth) =>
let
selectColumns = Table.SelectColumns(standardiseHeaders, {currentMonth & "tim", currentMonth}, MissingField.UseNull),
renameColumns = Table.RenameColumns(selectColumns, {{currentMonth & "tim", "Date"}, {currentMonth, "Value"}}),
appendToTable = Table.Combine({tableState, renameColumns})
in
appendToTable
),
tableOrNull = if List.Contains(Table.ColumnNames(standardiseHeaders), "januari") then appendEachMonth else null
in
tableOrNull,
getDataFromWorkbook = (filePath as text) =>
let
workbookContents = Excel.Workbook(File.Contents(filePath)),
sheetsOnly = Table.SelectRows(workbookContents, each [Kind] = "Sheet"),
invokeFunction = Table.AddColumn(sheetsOnly, "f", each getDataFromSheet([Data]), type table),
appendAndExpand =
let
selectColumnsAndRows = Table.SelectColumns(Table.SelectRows(invokeFunction, each not ([f] is null)), {"Name", "f"}),
renameColumns = Table.RenameColumns(selectColumnsAndRows, {{"Name", "Sheet"}}),
expandColumn = Table.ExpandTableColumn(renameColumns, "f", {"Date", "Value"})
in
expandColumn
in
appendAndExpand,
filesInFolder = Folder.Files(folderPath),
validFilesOnly = Table.SelectRows(filesInFolder, each [Extension] = ".xlsx"),
invokeFunction = Table.AddColumn(validFilesOnly, "f", each getDataFromWorkbook([Folder Path] & [Name])),
appendAndExpand =
let
selectRowsAndColumns = Table.SelectColumns(Table.SelectRows(invokeFunction, each not ([f] is null)), {"Name", "f"}),
renameColumns = Table.RenameColumns(selectRowsAndColumns, {{"Name", "Workbook"}}),
expandColumn = Table.ExpandTableColumn(renameColumns, "f", {"Sheet", "Date", "Value"})
in
expandColumn,
excludeBlankDates = Table.SelectRows(appendAndExpand, each not (Text.StartsWith([Date], " "))),
transformTypes =
let
dateAndHour = Table.TransformColumns(excludeBlankDates, {{"Date", each Text.Split(_, " ")}}),
changeTypes = Table.TransformColumns(dateAndHour, {{"Workbook", Text.From, type text}, {"Sheet", Text.From, type text}, {"Date", each DateTime.From(_{0}) + #duration(0, Number.From(_{1}), 0, 0), type datetime}, {"Value", Number.From, type number}})
in
changeTypes
in
transformTypes
-
出于可靠性和鲁棒性的考虑，如果您创建一个文件夹并将所有(需要重组的)Excel文件放入该文件夹中，并确保没有其他内容进入该文件夹(甚至不会进入该文件夹的文件，那将是很好的选择)进行导入/重组).
For reliability and robustness, it would be good if you create a folder and put all Excel files (that need restructuring) into that folder -- and ensure nothing else goes into that folder (not even the file that will be doing the importing/restructuring).
如果由于某种原因无法执行此操作，请在查询编辑器中单击
validFilesOnly步骤并修改过滤条件，以使表仅包含要重组的文件.If you can't do this for whatever reason, then click the
validFilesOnlystep whilst in the Query Editor and amend the filter criteria, such that the table only includes files you want restructured.这篇关于将一列移到另一列的末尾的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
