Блог

Я пытаюсь объединить много книг с несколькими листами. Проблема в том, что на листе 1 есть большой заголовок информации, предшествующий информации, необходимой для извлечения. А также множество объединенных ячеек, которые возвращают большое количество нулей и помещают данные в переменные столбцы в зависимости от даты и версии исходных книг.

В настоящее время сортировка и продвижение заголовков позволяет мне сопоставлять первые два столбца необходимой информации, но последующая информация помещается прямо в другие поля.

Есть ли способ удалить нули и переместить наборы данных влево, чтобы они соответствовали полям? Или еще лучше определить динамические изменения заголовка и вернуть данные, соответствующие выбранным заголовкам?

Ниже приводится краткое описание проблемы, к сожалению, очистка данных об объеме листов и книг на самом деле не является вариантом. Я довольно новичок в Power Query и, похоже, не могу разобраться в этом.

 c1    c2    c3    c4   c5    c6    c7
A     B    Null   C    D    Null   E
a     b    c      D    Null  E     Null
A     B    C     Null  D     G     E
 

Нужно только A-B-C-D-E.

 = () => let
    Source = Folder.Files("C:UsersXXXXXXXXDesktopLog"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Log", each #"Transform File from Log"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Log"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Log", Table.ColumnNames(#"Transform File from Log"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Name", type text}, {"Data", type any}, {"Item", type text}, {"Kind", type text}, {"Hidden", type logical}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Data", "Name", "Source.Name"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Name] = "page 1" or [Name] = "page 2" or [Name] = "page 2  " or [Name] = "page 3  " or [Name] = "page 4  " or [Name] = "page 5  " or [Name] = "page 6  " or [Name] = "page 7  " or [Name] = "page 8  ")),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Source.Name", "Name", "Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Reordered Columns", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", "Data.Column8", "Data.Column9", "Data.Column10", "Data.Column11", "Data.Column12", "Data.Column13", "Data.Column14", "Data.Column15", "Data.Column16", "Data.Column17", "Data.Column18", "Data.Column19", "Data.Column20", "Data.Column21", "Data.Column22", "Data.Column23", "Data.Column24", "Data.Column25", "Data.Column26", "Data.Column27", "Data.Column28", "Data.Column29", "Data.Column30", "Data.Column31", "Data.Column32", "Data.Column33", "Data.Column34", "Data.Column35", "Data.Column36", "Data.Column37", "Data.Column38", "Data.Column39", "Data.Column40", "Data.Column41", "Data.Column42", "Data.Column43", "Data.Column44", "Data.Column45", "Data.Column46", "Data.Column47", "Data.Column48", "Data.Column49", "Data.Column50", "Data.Column51", "Data.Column52", "Data.Column53", "Data.Column54", "Data.Column55", "Data.Column56", "Data.Column57", "Data.Column58", "Data.Column59", "Data.Column60", "Data.Column61", "Data.Column62", "Data.Column63", "Data.Column64", "Data.Column65", "Data.Column66", "Data.Column67", "Data.Column68", "Data.Column69", "Data.Column70", "Data.Column71", "Data.Column72", "Data.Column73", "Data.Column74", "Data.Column75", "Data.Column76", "Data.Column77", "Data.Column78", "Data.Column79", "Data.Column80", "Data.Column81", "Data.Column82", "Data.Column83", "Data.Column84"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Data", each ([Data.Column2] <> null and [Data.Column2] <> 16 and [Data.Column2] <> "16" and [Data.Column2] <> "LOCATION")),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows1", [PromoteAllScalars=true])
in
    #"Promoted Headers"
 

Изображение

Чтобы избавиться от нулей и сдвинуть все влево

добавить столбец .. столбец индекса

щелкните правой кнопкой мыши столбец индекса, открепите другие столбцы

щелкните правой кнопкой мыши и удалите столбец атрибутов

Сгруппируйте по индексу и добавьте еще один индекс в каждую группу, изменив код так, чтобы он заканчивался

 each Table.AddIndexColumn(_, "Index2", 1, 1), type table}})
 

Разверните столбец, используя стрелки вверху, для полей [x]значения и [x] индекс2

Щелкните поле Index2 и преобразуйте .. сводный столбец со значением в качестве значений, дополнительно, не агрегировать

Пример кода для преобразования приведенной выше таблицы ПЕРЕД таблицей в таблицу ПОСЛЕ

 let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
#"Grouped Rows"  = Table.Group(#"Removed Columns", {"Index"}, {{"GRP", each Table.AddIndexColumn(_, "Index2", 1, 1), type table}}),
#"Expanded GRP" = Table.ExpandTableColumn(#"Grouped Rows", "GRP", {"Value", "Index2"}, {"Value", "Index2"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded GRP", {{"Index2", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded GRP", {{"Index2", type text}}, "en-US")[Index2]), "Index2", "Value"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in #"Removed Columns1"