Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
In Power Query I would like to remove all columns that do'nt have a header. So I tried to find out how to remove all columns starting with "Column" in the column name. I would like to do this dynamicly because my source files may a different ammount of collumns without headers.
Can this be done in Power Query M script?
Solved! Go to Solution.
Well, in theory you could use Table.ColumnNames to get a list of column names, do some text parsing with Text functions, essentially loop through each value in the Text list coming from ColumnNames and build up a new set that only includes the word Column in them and then feed in the resulting list into Table.RemoveColumns function.
First and last parts are easy. The tricky part is the middle part. I would use Text.Split to split it out to a list because there are a lot of list functions that you could use to potentially do what you want.
https://msdn.microsoft.com/en-us/library/mt211003.aspx
Usually I would transpose the columns.
Then under the 1st column, i will remove the 'null' values.
Then re-transpose the columns again before promoting headers.
This is how I solved it.
Note: this returns all columns that contain the input string (e.g. "Column"), not just those starting with that string.
let Source = #"Query Containing Table", #"Column Names" = Table.ColumnNames(Source), #"Columns Containing String" = List.FindText(#"Column Names", "Column"), #"List Difference" = List.Difference(#"Column Names", #"Columns Containing String"), #"Remove Columns" = Table.RemoveColumns(Source, #"List Difference") in #"Remove Columns"
Good solution! Alternatively, this code is simpler:
= Table.RemoveColumns(#"Last step",List.FindText(Table.ColumnNames(#"Last step"), "Column"))
This really works great and is easy to implement! Thank you! 🙂
Well, in theory you could use Table.ColumnNames to get a list of column names, do some text parsing with Text functions, essentially loop through each value in the Text list coming from ColumnNames and build up a new set that only includes the word Column in them and then feed in the resulting list into Table.RemoveColumns function.
First and last parts are easy. The tricky part is the middle part. I would use Text.Split to split it out to a list because there are a lot of list functions that you could use to potentially do what you want.
https://msdn.microsoft.com/en-us/library/mt211003.aspx
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |