Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
nicolast29
Helper V
Helper V

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

nicolast29_0-1685628683882.png

 

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 :

 

https://1drv.ms/x/s!AoqtZHsX4BzvhLMY4mWpyWCh89cNaA?e=E4uOGC

1 ACCEPTED SOLUTION

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"

View solution in original post

6 REPLIES 6
lbendlin
Super User
Super User

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

nicolast29_0-1685873360849.png

 

and for the Z files it is at the 6 

nicolast29_1-1685873537174.png

 

Y and Z files 

https://1drv.ms/x/s!AoqtZHsX4BzvhLMjiNQpBLD1mUR7UQ?e=svKdoE

https://1drv.ms/x/s!AoqtZHsX4BzvhLMi0sHeIqJ17teI3g?e=LACnJt

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"

Thanks a lot !!!!

I will read and read again your code

 

Have a nice day🤘

 

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.