MS Excel

Dá sa riešiť pomocou PowerQuery a kontingenčných tabuliek (v prílohe).citovat

mepexg: PQ je velmi elegantni reseni, mozna bych jenom prehodil poradi kroku - promote headers se mne osvedcilo udelat jeste pred tim, nez expanduji data (a pak se zbavim problemu s filtrovanim chyb). Kod by mohl vypadat takto:

let
Source = Folder.Files("q\"),
#"Filtered Rows" = Table.SelectRows(Source, each [Extension] = ".xlsx"),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content", "Name"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Custom", each ([Kind] = "Sheet") and ([Name.1] = "Sheet1")),
#"Added Custom1" = Table.AddColumn(#"Filtered Rows1", "Custom", each Table.PromoteHeaders([Data])),
#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom1",{"Name", "Custom"}),
#"Expanded Custom1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom", {"29", "Column3", "Column4", "Column5", "Hodnota", "Meno"}, {"29", "Column3", "Column4", "Column5", "Hodnota", "Meno"}),
#"Filtered Rows2" = Table.SelectRows(#"Expanded Custom1", each ([Hodnota] <> "")),
#"Removed Other Columns2" = Table.SelectColumns(#"Filtered Rows2",{"Meno", "Hodnota"})
in
#"Removed Other Columns2"citovat