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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tc5pt
Regular Visitor

Remove columns containing a certain value

I have various columns that have a date listed in one row, therefore they contain a "/". None of the other columns I need contain a backslash in any of the rows. I would like to delete the columns containing this backslash.

 

I saw a thread that propose the following formula but am having difficulty applying it to my query. It tells me "PreviousStepName not recognized". I replaced it with the name of the previous step in my query, but that didn't work. Am I supposed to be filling in values for "ColumnNames" and other parts of this formula as well? Or is this formula not accurate for what I am trying to achieve?

 

= let columnsToRemove = List.Select(Table.ColumnNames(PreviousStepName), each Text.StartsWith(_, "Var") or Text.Contains(_, "_prep")) in Table.RemoveColumns(PreviousStepName, columnsToRemove)

 

I also found a thread that suggested transposing my data and then deleting rows, but this was not a good solution for my data.

 

Thank you

1 ACCEPTED SOLUTION
Nolock
Resident Rockstar
Resident Rockstar

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

11 REPLIES 11
Nolock
Resident Rockstar
Resident Rockstar

Hi @tc5pt,

I'm just guessing: You have to replace all occurancies of PreviousStepName. In your case you have to replace it twice.

 

let 
    PreviousStepName = <THE NAME OF PREVIOUS STEP>,
    columnsToRemove = List.Select(Table.ColumnNames(PreviousStepName), each Text.StartsWith(_, "Var") or Text.Contains(_, "_prep")) 
in 
    Table.RemoveColumns(PreviousStepName, columnsToRemove)

But I suppose there can also be another problem because your code starts with "=". Please post the whole query to see that you use it correctly.

Thanks for your response @Nolock 

 

Where exactly do I enter this code? In the Advanced Editor? When I do that it gives me either Token Comma or Token Eof expected. I am not sure how to input this code.

Nolock
Resident Rockstar
Resident Rockstar

Hi @tc5pt,

jep, in the Advanced Editor. Please post your PowerQuery query (without data) and I will help you to integrate the code.

The error message means that there is a comma missing or maybe one in the end of the code before the last IN or something similar.

@Nolock 

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])
in
    #"Promoted Headers"

This is what is currently in my advanced editor. It also Changed Type by default but I deleted that step because there were over 100 columns and it created a huge block of code.

 

How would I inject the code you gave?

Nolock
Resident Rockstar
Resident Rockstar

Hi @tc5pt,

here we go:

 

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]),
    columnsToRemove = List.Select(Table.ColumnNames(#"Promoted Headers"), each Text.StartsWith(_, "Var") or Text.Contains(_, "_prep")),
    result = Table.RemoveColumns(#"Promoted Headers", columnsToRemove)
in
    result

@Nolock 

This doesn't seem to be working and I'm not sure why. The columns' data type is text. I have made sure not to add any steps between (ie. no additional code has been added). Any ideas?

Hi, looking at the code sample you provided first and the requirement.  They don't do the same thing.  The code sample looks for column names that begin with "Var" or contain "_prep" and removes the column.

 

@Nolock(respect for the contribution you make to this site) helped you remove the syntax errors and, I think, assumed that the sample code was what you wanted.

I think (correct me if i'm wrong) that you want to remove columns where there is a row value that has "/" in it.

So, if the data looked like this:

column1 column2 column 3

A             FRE         6/2/2019

joe          23/4        x

sd            gt           y

 

you want to delete columns 2 and 3.  Yes?

I already changed that part of the code. I had changed the source and item/sheet names for the post and changed that back as well.

 

And yes, that's correct. Is it perhaps not the right code for that purpose?

 

Thank you @HotChilli 

Nolock
Resident Rockstar
Resident Rockstar

Hi @tc5pt and @HotChilli,

I'm very sorry, I unterstood the task wrong at the beginning.

I've written another code which removes columns that have at least one value containing a slash.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUrUB5FJQKwUqxMNZoBwsn4KTCgZxNBRMtQ3AovEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    // get columns which contains at least one value which contains a slash
    ColumnsToRemove = 
        List.Select(
            // get a list of all columns of the table Source
            Table.ColumnNames(Source),
            (columnName) =>
                let
                    // get all values of a column
                    ColumnValues = Table.Column(Source, 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(Source, ColumnsToRemove)
in
    Result

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 

Nolock
Resident Rockstar
Resident Rockstar

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors