Forum Discussion
Solved
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 m...
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"
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"Hi
Thanks for your answer
Your solution does the job on my example files, but for the real files i really need to filter base on the position as explain
here a real file without client information
https://1drv.ms/f/s!AoqtZHsX4BzvhLMk2vl7mHRT8F-OIQ?e=eNY7t5
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"