Forum Discussion

Mederic's avatar
Mederic
Post Patron
2 years ago

Extract range in a pdf

Hello, I'm working on a single pdf file. Once I've finished all the transformation steps, I'd like to turn it into a function However, I'm having trouble selecting the range in the table I've add...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Mederic 

You can try to use Table.RemoveMatchingRows() function.

e.g 

= Table.AddColumn(#"Changed Type1", "Custom", each if [Column3]="apple" then Table.RemoveMatchingRows(#"Changed Type1",{[Index=1]},"Index") else null)

 

You can refer to the following link about the function.

Table.RemoveMatchingRows - PowerQuery M | Microsoft Learn

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Mederic's avatar
Mederic
Post Patron
2 years ago

Hello Anonymous ,

Thank you for your message and for your help,
Last night, I got the result in screenshot with the code below
It seems to work even if I would have liked to see other more elegant solutions.
I still have to add the last columns but I'm currently Table.

Best Regards

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type any}, {"Column9", type text}, {"Column10", type any}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    RecordList = Table.AddColumn(#"Added Index", "Custom", each Record.ToList(_)),
    ListContains = Table.AddColumn(RecordList, "Custom.1", each List.ContainsAny([Custom], {"Reference"})),
    #"Filtered Rows" = Table.SelectRows(ListContains, each ([Custom.1] = true)),
    StartPointRows = #"Filtered Rows"{0}[Index],
    RefersChangedType = #"Changed Type",
    #"Removed Top Rows" = Table.Skip(RefersChangedType,StartPointRows-1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Reference", type text}, {"Designation", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Qty/Time", type text}, {"Column7", type text}, {"Column8", type any}, {"Unit Price", Int64.Type}, {"Column10", type any}, {"", type text}, {"Amount excl. taxes", Int64.Type}, {"Column13", Int64.Type}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each ([Unit Price] <> null)),
    SelectColumns = #"Filtered Rows1"[[Reference],[Designation],[#"Qty/Time"],[#"Unit Price"],[#"Amount excl. taxes"]]
in
    SelectColumns