Forum Discussion
Dynamicly delete x line
Hi
I have multiple Excel file to concatenate, but i should only start after that i found this information Section 7 on a specific sheet.
This information is not at the same line for each file
I made a function TrouveIndexSection to found the line of the information for each file, it works
I made another function SuppLignes to delete the line for each file, but i don't arrive to use the result of the previous function TrouveIndexSection as an argument for my function .... in order to delete the good number of line for each file
Here My file :
let Source = Folder.Files("C:\Users\xxx\Downloads"), #"Filtered Rows" = Table.SelectRows(Source, each [Name] = "ww.xlsx" or [Name] = "yy.xlsx" or [Name] = "ZZ.xlsx"), LoadFile = (Ex) => let #"Imported Excel Workbook" = Excel.Workbook(Ex), #"Fiche contrôle_Sheet" = #"Imported Excel Workbook"{[Item="Fiche contrôle",Kind="Sheet"]}[Data], #"Added Index" = Table.AddIndexColumn(#"Fiche contrôle_Sheet", "Index", 0, 1, Int64.Type), #"Removed Top Rows" = Table.Skip(#"Added Index",Table.SelectRows(#"Added Index", each ([Column2] = "Section 7 : RECOMMANDATIONS#(lf)"))[Index]{0}+1), #"Replaced Value" = Table.ReplaceValue(#"Removed Top Rows",null,"Category",Replacer.ReplaceValue,{"Column2"}), #"Removed Other Columns" = Table.SelectColumns(#"Replaced Value",{"Column2", "Column4"}), #"Promoted Headers" = Table.PromoteHeaders(#"Removed Other Columns", [PromoteAllScalars=true]) in #"Promoted Headers", #"Added Custom" = Table.AddColumn(#"Filtered Rows", "DataCustom", each LoadFile([Content])), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Name", "DataCustom"}), #"Expanded DataCustom" = Table.ExpandTableColumn(#"Removed Other Columns", "DataCustom", {"Category", "Thème"}, {"Category", "Thème"}), #"Filtered Rows1" = Table.SelectRows(#"Expanded DataCustom", each ([Thème] <> null)) in #"Filtered Rows1"
6 Replies
Not sure exactly what you are trying to achieve but have a look at this:
let Source = Excel.Workbook(File.Contents("C:\Users\xxx\Downloads\SUPPORT.xlsx"), null, true), Synthèse_test_Sheet = Source{[Item="Synthèse_test",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Synthèse_test_Sheet, [PromoteAllScalars=true]), #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each [Column2] <> null and Text.Lower([Column2])<>Text.Lower([Nom])) in #"Filtered Rows"Hi
thanks for your reply but it is not that i want
I try to explain in other way
I concatenate 2 files Y and Z
In the Y file i want to delete the 8 fist lines ant for Z the 6 and after concanetate the rest of lines of the 2 files
I found 8 and 6 because with the fist function i search Section 7 and for the Y file it is at the 8 position
and for the Z files it is at the 6
Y and Z files
let Source = Folder.Files("C:\Users\xxx\Downloads"), #"Filtered Rows" = Table.SelectRows(Source, each [Name] = "yy.xlsx" or [Name] = "ZZ.xlsx"), LoadFile = (Ex) => let #"Imported Excel Workbook" = Excel.Workbook(Ex), #"Fiche contrôle_Sheet" = #"Imported Excel Workbook"{[Item="Fiche contrôle",Kind="Sheet"]}[Data], #"Filtered Rows1" = Table.SelectRows(#"Fiche contrôle_Sheet", each ([Column4] <> null)), #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows1",null,"Category",Replacer.ReplaceValue,{"Column2"}), #"Removed Other Columns" = Table.SelectColumns(#"Replaced Value",{"Column2", "Column4"}), #"Promoted Headers" = Table.PromoteHeaders(#"Removed Other Columns", [PromoteAllScalars=true]) in #"Promoted Headers", #"Added Custom" = Table.AddColumn(#"Filtered Rows", "DataCustom", each LoadFile([Content])), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Name", "DataCustom"}), #"Expanded DataCustom" = Table.ExpandTableColumn(#"Removed Other Columns", "DataCustom", {"Category", "Thème"}, {"Category", "Thème"}) in #"Expanded DataCustom"
