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.
Hi,
I need to recognize if column of table contains only specific value (if it will contain specific values and other values, do nothing) . In case of TRUE, I need to remove this column/s. I need to do it with many columns contains data type "inteager" and "string" in one table.
Example:
Can you help me?
Jan
Solved! Go to Solution.
Once in Power Query, you have to make an assumption in order to map the column names to the column letter in Excel.
In the code below I am assuming that the first column of your PQ table is Column B in Excel. If that is not the case, you will need to edit the code that looks for the zero's and no's to look at the correct columns.
Read the code and the comments to understand the algorithm.
let
//edit next line to reflect actual data source
Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
//edit next line to reflect actual column headers
// or set data types using a dynamic method
#"Changed Type" = Table.TransformColumnTypes(Source,
{{"20220308", Int64.Type}, {"20220309", Int64.Type}, {"20220310", Int64.Type}, {"its possible 1", type text}, {"its possible 2", type text}, {"Its possible3", type text}, {"no remove", type text}}),
//ASSUMES table starts with column B (adjust list ranges if that is not the case)
// Check for 0's will be columns 1,2,3
// Check for "NO"'s will be columns 4,5,6
#"Check for Zero" = List.Range(Table.ColumnNames(#"Changed Type"),0,3),
#"Check for NO" = List.Range(Table.ColumnNames(#"Changed Type"),3,3),
#"All Zero" = List.Accumulate(#"Check for Zero",{}, (state, current)=>
if List.MatchesAll(Table.Column(#"Changed Type",current), each _ = 0) then state & {current} else state),
#"All NO" = List.Accumulate(#"Check for NO",{}, (state, current)=>
if List.MatchesAll(Table.Column(#"Changed Type",current), each _ = "NO") then state & {current} else state),
//Delete the relevant columns
#"Delete Columns" = Table.RemoveColumns(#"Changed Type", #"All Zero" & #"All NO")
in
#"Delete Columns"
Sorry I forgot mention that I can determinate which Value in which Column I seek.
In this example I determinate:
If in column range B:D is Column which has only 0 value, this will be removed.
If in column range E:G is Column which has only "NO" value, this will be removed.
Jan
Once in Power Query, you have to make an assumption in order to map the column names to the column letter in Excel.
In the code below I am assuming that the first column of your PQ table is Column B in Excel. If that is not the case, you will need to edit the code that looks for the zero's and no's to look at the correct columns.
Read the code and the comments to understand the algorithm.
let
//edit next line to reflect actual data source
Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
//edit next line to reflect actual column headers
// or set data types using a dynamic method
#"Changed Type" = Table.TransformColumnTypes(Source,
{{"20220308", Int64.Type}, {"20220309", Int64.Type}, {"20220310", Int64.Type}, {"its possible 1", type text}, {"its possible 2", type text}, {"Its possible3", type text}, {"no remove", type text}}),
//ASSUMES table starts with column B (adjust list ranges if that is not the case)
// Check for 0's will be columns 1,2,3
// Check for "NO"'s will be columns 4,5,6
#"Check for Zero" = List.Range(Table.ColumnNames(#"Changed Type"),0,3),
#"Check for NO" = List.Range(Table.ColumnNames(#"Changed Type"),3,3),
#"All Zero" = List.Accumulate(#"Check for Zero",{}, (state, current)=>
if List.MatchesAll(Table.Column(#"Changed Type",current), each _ = 0) then state & {current} else state),
#"All NO" = List.Accumulate(#"Check for NO",{}, (state, current)=>
if List.MatchesAll(Table.Column(#"Changed Type",current), each _ = "NO") then state & {current} else state),
//Delete the relevant columns
#"Delete Columns" = Table.RemoveColumns(#"Changed Type", #"All Zero" & #"All NO")
in
#"Delete Columns"
Thank you, it works great:).
Jan
@Kopec Removing column dynamically with PQ is easy however, there is no concept of range in PQ, so how would you deal with that? Once in PQ I can only utilise the column names and the values.
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.