Forum Discussion

Frequent Visitor

3 years ago

Solved

Capture Columns based on Key words in Column Headers and also the values with from 1 to 9

Hi all,

I have pasted sample data here, this is only for reference

Consumer	Product	Overall_Liking	Taste_Liking	Liking of Product	Liking_Ratings
1	Cookie	9	1	0	1
2	Cookie	8	2	1	2
3	Cookie	7	3	0	3
4	Cookie	6	4	1	4
5	Cookie	5	5	0	5
6	Cookie	4	6	1	5
7	Cookie	3	7	0	4
8	Cookie	2	8	1	3
9	Cookie	1	9	0	2

Now I have Columns with key words "Liking" in it and values in Overall_Liking, Taste_Liking are in the scale of 1 to 9, Liking of Product are in the scale of 0,1 and Liking_Ratings are in the scale of 1 to 5

What I want here is to capture the columns with Key Word "Liking" and values in the scale of 1 to 9 and remove the other columns, will it be possible to do this in Power Query!?

If so please help, Thanks in Advance!!

Need Help

AntrikshSharma

3 years ago

Kishore_SR Here are 2 methods:

with List.Accumulate:

let
    Source = Table.FromRows (
        Json.Document (
            Binary.Decompress (
                Binary.FromText (
                    "Tc89DsAgCAXgqzTOHVDxb+4xjGOHpoP334r0xTC8YPzQQO/Ou9Ndc77PLYcmWRekdZzdBctVEtASlKPlIol4HZXZcpYwXh+snqwnhLQuzpYZX/jNxXLEAKSti6vlgPH9nq1Z9lie/s3GBw==",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
        in
            type table [
                Consumer = _t,
                Product = _t,
                Overall_Liking = _t,
                Taste_Liking = _t,
                #"Liking of Product" = _t,
                Liking_Ratings = _t
            ]
    ),
    ChangedType = Table.TransformColumnTypes (
        Source,
        {
            { "Consumer", Int64.Type },
            { "Product", type text },
            { "Overall_Liking", Int64.Type },
            { "Taste_Liking", Int64.Type },
            { "Liking of Product", Int64.Type },
            { "Liking_Ratings", Int64.Type }
        }
    ),
    ColumnNames = List.Select (
        Table.ColumnNames ( ChangedType ),
        each Text.Contains ( Text.Lower ( _ ), "liking" )
    ),
    Transformation = List.Accumulate (
        ColumnNames,
        ChangedType,
        ( State, Current ) =>
            let
                Col = Table.Column ( State, Current ),
                Min = List.Min ( Col ),
                Max = List.Max ( Col ),
                Check =
                    if Min = 1 and Max = 9 then
                        State
                    else
                        Table.RemoveColumns ( State, Current )
            in
                Check
    )
in
    Transformation

Method 2:

let
    Source = Table.FromRows (
        Json.Document (
            Binary.Decompress (
                Binary.FromText (
                    "Tc89DsAgCAXgqzTOHVDxb+4xjGOHpoP334r0xTC8YPzQQO/Ou9Ndc77PLYcmWRekdZzdBctVEtASlKPlIol4HZXZcpYwXh+snqwnhLQuzpYZX/jNxXLEAKSti6vlgPH9nq1Z9lie/s3GBw==",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
        in
            type table [
                Consumer = _t,
                Product = _t,
                Overall_Liking = _t,
                Taste_Liking = _t,
                #"Liking of Product" = _t,
                Liking_Ratings = _t
            ]
    ),
    ChangedType = Table.TransformColumnTypes (
        Source,
        {
            { "Consumer", Int64.Type },
            { "Product", type text },
            { "Overall_Liking", Int64.Type },
            { "Taste_Liking", Int64.Type },
            { "Liking of Product", Int64.Type },
            { "Liking_Ratings", Int64.Type }
        }
    ),
    LikingsColumns = List.Select (
        Table.ColumnNames ( ChangedType ),
        each Text.Contains ( Text.Lower ( _ ), "liking" )
    ),
    NonLikingsColumns = List.Select (
        Table.ColumnNames ( ChangedType ),
        each not Text.Contains ( Text.Lower ( _ ), "liking" )
    ),
    ColumnsToKeep = List.Select (
        LikingsColumns,
        ( ColName ) =>
            let
                ColList = Table.Column ( ChangedType, ColName ),
                Min     = List.Min ( ColList ),
                Max     = List.Max ( ColList ),
                Check   = if Min = 1 and Max = 9 then true else false
            in
                Check
    ),
    Result = Table.SelectColumns ( ChangedType, NonLikingsColumns & ColumnsToKeep )
in
    Result

4 Replies

AntrikshSharma

Community Champion

3 years ago

Kishore_SR Here are 2 methods:

with List.Accumulate:

let
    Source = Table.FromRows (
        Json.Document (
            Binary.Decompress (
                Binary.FromText (
                    "Tc89DsAgCAXgqzTOHVDxb+4xjGOHpoP334r0xTC8YPzQQO/Ou9Ndc77PLYcmWRekdZzdBctVEtASlKPlIol4HZXZcpYwXh+snqwnhLQuzpYZX/jNxXLEAKSti6vlgPH9nq1Z9lie/s3GBw==",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
        in
            type table [
                Consumer = _t,
                Product = _t,
                Overall_Liking = _t,
                Taste_Liking = _t,
                #"Liking of Product" = _t,
                Liking_Ratings = _t
            ]
    ),
    ChangedType = Table.TransformColumnTypes (
        Source,
        {
            { "Consumer", Int64.Type },
            { "Product", type text },
            { "Overall_Liking", Int64.Type },
            { "Taste_Liking", Int64.Type },
            { "Liking of Product", Int64.Type },
            { "Liking_Ratings", Int64.Type }
        }
    ),
    ColumnNames = List.Select (
        Table.ColumnNames ( ChangedType ),
        each Text.Contains ( Text.Lower ( _ ), "liking" )
    ),
    Transformation = List.Accumulate (
        ColumnNames,
        ChangedType,
        ( State, Current ) =>
            let
                Col = Table.Column ( State, Current ),
                Min = List.Min ( Col ),
                Max = List.Max ( Col ),
                Check =
                    if Min = 1 and Max = 9 then
                        State
                    else
                        Table.RemoveColumns ( State, Current )
            in
                Check
    )
in
    Transformation

Method 2:

let
    Source = Table.FromRows (
        Json.Document (
            Binary.Decompress (
                Binary.FromText (
                    "Tc89DsAgCAXgqzTOHVDxb+4xjGOHpoP334r0xTC8YPzQQO/Ou9Ndc77PLYcmWRekdZzdBctVEtASlKPlIol4HZXZcpYwXh+snqwnhLQuzpYZX/jNxXLEAKSti6vlgPH9nq1Z9lie/s3GBw==",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
        in
            type table [
                Consumer = _t,
                Product = _t,
                Overall_Liking = _t,
                Taste_Liking = _t,
                #"Liking of Product" = _t,
                Liking_Ratings = _t
            ]
    ),
    ChangedType = Table.TransformColumnTypes (
        Source,
        {
            { "Consumer", Int64.Type },
            { "Product", type text },
            { "Overall_Liking", Int64.Type },
            { "Taste_Liking", Int64.Type },
            { "Liking of Product", Int64.Type },
            { "Liking_Ratings", Int64.Type }
        }
    ),
    LikingsColumns = List.Select (
        Table.ColumnNames ( ChangedType ),
        each Text.Contains ( Text.Lower ( _ ), "liking" )
    ),
    NonLikingsColumns = List.Select (
        Table.ColumnNames ( ChangedType ),
        each not Text.Contains ( Text.Lower ( _ ), "liking" )
    ),
    ColumnsToKeep = List.Select (
        LikingsColumns,
        ( ColName ) =>
            let
                ColList = Table.Column ( ChangedType, ColName ),
                Min     = List.Min ( ColList ),
                Max     = List.Max ( ColList ),
                Check   = if Min = 1 and Max = 9 then true else false
            in
                Check
    ),
    Result = Table.SelectColumns ( ChangedType, NonLikingsColumns & ColumnsToKeep )
in
    Result

wdx223_Daniel
Community Champion
3 years ago
= Table.SelectColumns(Source,List.Select(Table.ColumnNames(PreviousStepName),each not Text.Contains(_,"Liking") or List.RemoveItems(Table.Column(PreviousStepName,_),{1..9})={}))
Kishore_SR
Frequent Visitor
3 years ago
Hi AntrikshSharma

Your Method 2 works great for me, Thank you for your help.

One concern for me is, instead of Max = 9 can we set it as greater than 5..!! because sometimes there might not be no 9 ratings given

Is there a work around for this!?
- AntrikshSharma
  Community Champion
  3 years ago
  Kishore_SR Then you can do
  Check = if Min = 1 and Max > 5 then true else false

Forum Discussion

Capture Columns based on Key words in Column Headers and also the values with from 1 to 9

4 Replies

Related content

Dynamic Column headers

Dynamic Column Header in Visualisation

Line Break in Table Column Headers

Customize Column header tooltip

Dynamic Column Header In a Matrix