Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
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.
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.
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?
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
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
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
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