let
Source = Csv.Document(File.Contents("C:\Demo.csv"), null,",",null,1252),
#"First Row as Header" = Table.PromoteHeaders(Source),
#"Changed Type" = Table.TransformColumnTypes(#"First Row as Header",{{"Sales", Int64.Type}})
in
#"Changed Type"
#table(
type table [Product=text, Month=text, Sales=number],
{})
let
Source = Excel.CurrentWorkbook(){[Name="Columns"]}[Content],
#"Transposed Table" = Table.Transpose(Source),
#"First Row as Header" = Table.PromoteHeaders(#"Transposed Table"),
#"Changed Type" = Table.TransformColumnTypes(#"First Row as Header",
{{"Product", type text}, {"Month", type text}, {"Sales", Int64.Type}})
in
#"Changed Type"
let
Source = GetSourceData,
Append = Table.Combine({Source,ExpectedColumns}),
#"Removed Other Columns" = Table.SelectColumns(Append,{"Product", "Month", "Sales"})
in
#"Removed Other Columns"
let
//Соединимся с таблицей Excel, содержащей ожидаемые заголовки
ExcelSource = Excel.CurrentWorkbook(){[Name="Columns"]}[Content],
//Получаем список ожидаемых заголовков
ExpectedColumns = Table.Column(ExcelSource, "ColumnName"),
//Список заголовков файла csv
CSVColumns = Table.ColumnNames(GetSourceData),
//Ищем отсутствующие столбцы
MissingColumns = List.Difference(ExpectedColumns, CSVColumns),
//Ищем добавленные столбцы
AddedColumns = List.Difference(CSVColumns, ExpectedColumns),
//Все изменения
OutputMissing = if List.Count(MissingColumns)=0 then
"No columns missing" else
"Missing columns: " & Text.Combine(MissingColumns, ","),
OutputAdded = if List.Count(AddedColumns)=0 then
"No columns added" else
"Added columns: " & Text.Combine(AddedColumns, ","),
Output = OutputMissing & " " & OutputAdded
in
Output
let
Source = Csv.Document(File.Contents("C:\MissingColumnDemo.csv"),[Delimiter=",",Encoding=1252]),
PromotedHeaders = Table.PromoteHeaders(Source),
CheckColumns = Table.HasColumns(PromotedHeaders, {"Product", "Month", "Sales"})
in
CheckColumns