cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tc5pt Frequent Visitor
Frequent Visitor

Re: Remove columns containing a certain value

Thank you. I will try this out. Do I need to be replacing certain words in there, like "source"?

 

I am a total noob so I apologize if it's obvious.

 

Thank you @Nolock 

Super User
Super User

Re: Remove columns containing a certain value

Hi @tc5pt,

I've tried to integrate it into the code you mentioned earlier. Just replace all occurrences of "Source" with #"Promoted Headers" and it should work in your Excel file.

 

let
    Source = Excel.Workbook(File.Contents("C: Source"), null, true),
    #"1_Sheet" = Source{[Item="1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"1_Sheet", [PromoteAllScalars=true]),

// get columns which contains any slash among values
    ColumnsToRemove = 
        List.Select(
            // get a list of all columns
            Table.ColumnNames(#"Promoted Headers"),
            (columnName) =>
                let
                    // get all values of a columns
                    ColumnValues = Table.Column(#"Promoted Headers", columnName),
                    // go through values and stop when you find the first occurence of a text containing a slash
                    // if there is a value with a slash, return true else false
                    ContainsSlash = List.AnyTrue(List.Transform(ColumnValues, each Text.Contains(_, "/")))
                in
                    ContainsSlash
        ),
    // remove columns
    Result = Table.RemoveColumns(#"Promoted Headers", ColumnsToRemove)
in
    Result

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (4,077)