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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Kishore_SR
Frequent Visitor

Data Filtering and Delete unwanted rows in Query Editor

Hello,

 

In the Image pasted below, You can see Selection and Liking_Metrics column,

 

In those two columns, PRODUCT_1ST and PRODUCT_2ND value in Selection column is only related to value Overall Liking in the column Liking_metrics, and there are other values in column Liking_Metrics are only related to PRODUCT_1ST value in Selection column, so now I want to removes PRODUCT_2ND value rows from the table related to values other than Overall Liking in Liking Metrics

Kishore_SR_0-1663235953081.png

I have attached Sample PBIX file in the drive for your reference

 

Thanks In Advance....

 

https://drive.google.com/file/d/1-Eb-T3uAT6m-Q_nPIAKQKkf4GAohe33s/view?usp=sharing

 

 

 

 

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

Hi  @Kishore_SR ,

You can apply the filter on the query "Liking Attributes" just as below screeshot:

let
    Source = Excel.Workbook(File.Contents("C:\Users\kishore.mohanraj\Desktop\PowerBI\Mondelez\Copy_MDZPQB TOTAL DATA _5 June 2022.xlsx"), null, true),
    #"DATA FOR Non Time Jobs_Sheet" = Source{[Item="DATA FOR Non Time Jobs",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"DATA FOR Non Time Jobs_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Serial", type text}, {"TEST_ID", type text}, {"AGENCY_ID", Int64.Type}, {"CONSUMER_ID", Int64.Type}, {"PRODUCT", type text}, {"YEAR", Int64.Type}, {"PNUMBER", Int64.Type}, {"CATEGORY", type text}, {"CATEGORY_CODE", type text}, {"BRAND", type text}, {"COUNTRY", type text}, {"PROJECT_TYPE", type text}, {"PROJECT_TITLE", type text}, {"PRODUCT_MONDELEZ", type text}, {"PRODUCT_CODE_MONDELEZ", Int64.Type}, {"PRODUCT_COMPETITOR", type text}, {"PRODUCT_CODE_COMPETITOR", Int64.Type}, {"ACTION_STANDARD", type text}, {"METHOD", type text}, {"VENUE", type text}, {"LOCATIONS", type text}, {"DATE_OF_FIELDWORK", Int64.Type}, {"SAMPLE_SIZE", Int64.Type}, {"SAMPLE", type text}, {"1ST", Int64.Type}, {"PRODUCT_1ST", type text}, {"2ND", Int64.Type}, {"PRODUCT_2ND", type text}, {"OL_1ST", Int64.Type}, {"PI_1ST", Int64.Type}, {"OL_2ND", Int64.Type}, {"PI_2ND", Int64.Type}, {"LIK_002", Int64.Type}, {"LIK_003", Int64.Type}, {"LIK_004", type any}, {"LIK_005", type any}, {"LIK_006", Int64.Type}, {"LIK_007", Int64.Type}, {"LIK_008", Int64.Type}, {"LIK_009", Int64.Type}, {"LIK_012", Int64.Type}, {"LIK_013", type any}, {"LIK_017", Int64.Type}, {"LIK_018", type any}, {"LIK_019", type any}, {"LIK_020", type any}, {"LIK_021", type any}, {"LIK_022", type any}, {"LIK_023", type any}, {"LIK_024", type any}, {"LIK_025", type any}, {"LIK_026", type any}, {"LIK_027", type any}, {"LIK_028", type any}, {"LIK_029", type any}, {"LIK_030", type any}, {"LIK_031", type any}, {"LIK_033", type any}, {"JAR_101", Int64.Type}, {"JAR_102", Int64.Type}, {"JAR_103", type any}, {"JAR_104", type any}, {"JAR_105", type any}, {"JAR_106", type any}, {"JAR_110", type any}, {"JAR_115", type any}, {"JAR_116", type any}, {"JAR_118", Int64.Type}, {"JAR_119", type any}, {"JAR_120", type any}, {"JAR_122", type any}, {"JAR_123", type any}, {"JAR_124", type any}, {"JAR_125", type any}, {"JAR_126", type any}, {"JAR_127", type any}, {"JAR_128", type any}, {"JAR_129", type any}, {"JAR_131", type any}, {"JAR_133", type any}, {"JAR_135", type any}, {"JAR_137", type any}, {"JAR_138", Int64.Type}, {"JAR_139", Int64.Type}, {"JAR_141", Int64.Type}, {"JAR_142", type any}, {"JAR_145", type any}, {"JAR_146", type any}, {"JAR_147", type any}, {"JAR_148", type any}, {"JAR_149", type any}, {"JAR_150", type any}, {"JAR_151", type any}, {"JAR_152", type any}, {"JAR_153", type any}, {"JAR_154", type any}, {"JAR_155", type any}, {"JAR_156", type any}, {"JAR_158", type any}, {"JAR_159", type any}, {"JAR_160", type any}, {"JAR_163", type any}, {"JAR_164", type any}, {"JAR_165", type any}, {"JAR_167", type any}, {"JAR_168", Int64.Type}, {"JAR_169", type any}, {"JAR_170", type any}, {"JAR_174", type any}, {"JAR_175", type any}, {"JAR_178", type any}, {"JAR_179", type any}, {"JAR_180", type any}, {"JAR_182", type any}, {"JAR_183", type any}, {"JAR_184", type any}, {"JAR_185", type any}, {"JAR_186", type any}, {"JAR_187", type any}, {"JAR_188", type any}, {"JAR_190", type any}, {"JAR_195", type any}, {"JAR_196", type any}, {"JAR_205", type any}, {"JAR_208", type any}, {"JAR_209", type any}, {"JAR_210", type any}, {"JAR_211", type any}, {"JAR_213", type any}, {"JAR_214", type any}, {"JAR_216", type any}, {"JAR_217", type any}, {"JAR_218", type any}, {"JAR_219", type any}, {"JAR_220", type any}, {"JAR_222", type any}, {"JAR_223", type any}, {"JAR_224", type any}, {"JAR_225", type any}, {"JAR_226", type any}, {"JAR_227", type any}, {"JAR_230", type any}, {"JAR_231", type any}, {"JAR_234", Int64.Type}, {"JAR_235", type any}, {"JAR_236", type any}, {"JAR_237", type any}, {"JAR_238", type any}, {"JAR_239", type any}, {"JAR_241", type any}, {"JAR_242", type any}, {"JAR_243", type any}, {"JAR_245", type any}, {"JAR_246", type any}, {"JAR_247", type any}, {"JAR_248", type any}, {"JAR_249", type any}, {"JAR_251", type any}, {"JAR_252", type any}, {"JAR_253", type any}, {"JAR_254", type any}, {"JAR_255", type any}, {"JAR_256", type any}, {"JAR_257", type any}, {"JAR_258", type any}, {"JAR_259", type any}, {"JAR_260", type any}, {"JAR_264", type any}, {"JAR_265", type any}, {"JAR_266", type any}, {"JAR_267", type any}, {"JAR_268", type any}, {"JAR_269", type any}, {"JAR_270", type any}, {"INT_001", type any}, {"INT_002", Int64.Type}, {"INT_003", Int64.Type}, {"INT_004", Int64.Type}, {"INT_005", Int64.Type}, {"INT_007", type any}, {"INT_009", type any}, {"INT_012", type any}, {"INT_013", type any}, {"INT_014", type any}, {"INT_015", Int64.Type}, {"INT_016", Int64.Type}, {"INT_017", type any}, {"INT_018", type any}, {"INT_019", type any}, {"INT_020", type any}, {"INT_021", type any}, {"INT_022", type any}, {"INT_023", type any}, {"INT_024", type any}, {"INT_025", type any}, {"INT_026", Int64.Type}, {"INT_027", type any}, {"INT_028", type any}, {"PREF_OVERALL", Int64.Type}, {"PREF_002", Int64.Type}, {"PREF_003", Int64.Type}, {"PREF_004", type any}, {"PREF_005", type any}, {"PREF_006", Int64.Type}, {"PREF_007", Int64.Type}, {"PREF_008", Int64.Type}, {"PREF_009", Int64.Type}, {"PREF_101", type any}, {"PREF_102", type any}, {"PREF_103", type any}, {"PREF_104", type any}, {"PREF_105", type any}, {"PREF_106", type any}, {"PREF_115", type any}, {"PREF_118", type any}, {"PREF_125", type any}, {"PREF_127", type any}, {"PREF_137", type text}, {"PREF_150", type any}, {"PREF_163", type text}, {"PREF_164", type any}, {"PREF_214", type any}, {"PREF_215", Int64.Type}, {"PREF_218", type any}, {"PREF_219", type any}, {"PREF_220", type any}, {"PREF_221", type text}, {"PREF_222", type text}, {"PREF_223", type text}, {"PREF2_OVERALL", type text}, {"PREF2_002", type text}, {"PREF2_003", type text}, {"PREF2_004", type text}, {"PREF2_005", type text}, {"PREF2_006", type text}, {"PREF2_007", type text}, {"PREF2_008", type text}, {"PREF2_009", type text}, {"PREF2_101", type text}, {"PREF2_102", type text}, {"PREF2_103", type text}, {"PREF2_104", type text}, {"PREF2_105", type text}, {"PREF2_106", type text}, {"PREF2_115", type text}, {"PREF2_118", type text}, {"PREF2_125", type text}, {"PREF2_127", type text}, {"PREF2_137", type text}, {"PREF2_150", type text}, {"PREF2_163", type text}, {"PREF2_164", type text}, {"PREF2_214", type text}, {"PREF2_215", type text}, {"PREF2_218", type text}, {"PREF2_219", type text}, {"PREF2_220", type text}, {"PREF2_221", type text}, {"PREF2_222", type text}, {"PREF2_223", type text}, {"BRAND_REC_1", type text}, {"BRAND_1", type text}, {"BRAND_REC_2", type text}, {"BRAND_2", type text}, {"GENDER", type text}, {"AGEGR", type text}, {"FREQCONSUMED_L12", type text}, {"FREQCONSUMED_L12G", type text}, {"BRANDFREQ", type text}, {"BRANDS_REG", type text}, {"BRANDS_MOB", type text}, {"BRANDS_NEVER", type text}, {"MARITALSTATUS", type text}, {"EDUCATION_LEVEL", type text}, {"NUMBER_OF_FAMILY_MEMBERS_IN_HH", type text}, {"CHILDREN_UP_TO_16_YO_IN_HH", type text}, {"WORK", type text}, {"FINANCIAL_SITUATION_IN_HH", type text}, {"LOC", type text}, {"Image URL_M", type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Serial", "PNUMBER", "PRODUCT_CODE_MONDELEZ", "PRODUCT_CODE_COMPETITOR", "VENUE", "LOCATIONS", "DATE_OF_FIELDWORK", "SAMPLE_SIZE", "SAMPLE", "PI_1ST", "PI_2ND", "JAR_101", "JAR_102", "JAR_103", "JAR_104", "JAR_105", "JAR_106", "JAR_110", "JAR_115", "JAR_116", "JAR_118", "JAR_119", "JAR_120", "JAR_122", "JAR_123", "JAR_124", "JAR_125", "JAR_126", "JAR_127", "JAR_128", "JAR_129", "JAR_131", "JAR_133", "JAR_135", "JAR_137", "JAR_138", "JAR_139", "JAR_141", "JAR_142", "JAR_145", "JAR_146", "JAR_147", "JAR_148", "JAR_149", "JAR_150", "JAR_151", "JAR_152", "JAR_153", "JAR_154", "JAR_155", "JAR_156", "JAR_158", "JAR_159", "JAR_160", "JAR_163", "JAR_164", "JAR_165", "JAR_167", "JAR_168", "JAR_169", "JAR_170", "JAR_174", "JAR_175", "JAR_178", "JAR_179", "JAR_180", "JAR_182", "JAR_183", "JAR_184", "JAR_185", "JAR_186", "JAR_187", "JAR_188", "JAR_190", "JAR_195", "JAR_196", "JAR_205", "JAR_208", "JAR_209", "JAR_210", "JAR_211", "JAR_213", "JAR_214", "JAR_216", "JAR_217", "JAR_218", "JAR_219", "JAR_220", "JAR_222", "JAR_223", "JAR_224", "JAR_225", "JAR_226", "JAR_227", "JAR_230", "JAR_231", "JAR_234", "JAR_235", "JAR_236", "JAR_237", "JAR_238", "JAR_239", "JAR_241", "JAR_242", "JAR_243", "JAR_245", "JAR_246", "JAR_247", "JAR_248", "JAR_249", "JAR_251", "JAR_252", "JAR_253", "JAR_254", "JAR_255", "JAR_256", "JAR_257", "JAR_258", "JAR_259", "JAR_260", "JAR_264", "JAR_265", "JAR_266", "JAR_267", "JAR_268", "JAR_269", "JAR_270", "INT_001", "INT_002", "INT_003", "INT_004", "INT_005", "INT_007", "INT_009", "INT_012", "INT_013", "INT_014", "INT_015", "INT_016", "INT_017", "INT_018", "INT_019", "INT_020", "INT_021", "INT_022", "INT_023", "INT_024", "INT_025", "INT_026", "INT_027", "INT_028", "PREF_OVERALL", "PREF_002", "PREF_003", "PREF_004", "PREF_005", "PREF_006", "PREF_007", "PREF_008", "PREF_009", "PREF_101", "PREF_102", "PREF_103", "PREF_104", "PREF_105", "PREF_106", "PREF_115", "PREF_118", "PREF_125", "PREF_127", "PREF_137", "PREF_150", "PREF_163", "PREF_164", "PREF_214", "PREF_215", "PREF_218", "PREF_219", "PREF_220", "PREF_221", "PREF_222", "PREF_223", "PREF2_OVERALL", "PREF2_002", "PREF2_003", "PREF2_004", "PREF2_005", "PREF2_006", "PREF2_007", "PREF2_008", "PREF2_009", "PREF2_101", "PREF2_102", "PREF2_103", "PREF2_104", "PREF2_105", "PREF2_106", "PREF2_115", "PREF2_118", "PREF2_125", "PREF2_127", "PREF2_137", "PREF2_150", "PREF2_163", "PREF2_164", "PREF2_214", "PREF2_215", "PREF2_218", "PREF2_219", "PREF2_220", "PREF2_221", "PREF2_222", "PREF2_223", "Image URL_M", "BRAND_REC_1", "BRAND_1", "BRAND_REC_2", "BRAND_2", "GENDER", "AGEGR", "FREQCONSUMED_L12", "FREQCONSUMED_L12G", "BRANDFREQ", "BRANDS_REG", "BRANDS_MOB", "BRANDS_NEVER", "MARITALSTATUS", "EDUCATION_LEVEL", "NUMBER_OF_FAMILY_MEMBERS_IN_HH", "CHILDREN_UP_TO_16_YO_IN_HH", "WORK", "FINANCIAL_SITUATION_IN_HH", "LOC"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"YEAR", type text}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type1", {"OL_1ST","OL_2ND","LIK_002", "LIK_003", "LIK_004", "LIK_005", "LIK_006", "LIK_007", "LIK_008", "LIK_009", "LIK_012", "LIK_013", "LIK_017", "LIK_018", "LIK_019", "LIK_020", "LIK_021", "LIK_022", "LIK_023", "LIK_024", "LIK_025", "LIK_026", "LIK_027", "LIK_028", "LIK_029", "LIK_030", "LIK_031", "LIK_033"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Only Selected Columns",{{"Attribute", "Like_Attribute"}, {"Value", "Like_Value"}}),
    #"Unpivoted Only Selected Columns1" = Table.Unpivot(#"Renamed Columns", {"PRODUCT_1ST", "PRODUCT_2ND"}, "Attribute", "Value"),
    #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Only Selected Columns1", "Liking_Metrics", each if [Like_Attribute] = "LIK_002" then "Appearence" else if [Like_Attribute] = "LIK_003" then "Flavor / Taste" else if [Like_Attribute] = "LIK_004" then "Texture" else if [Like_Attribute] = "LIK_005" then "Aroma" else if [Like_Attribute] = "LIK_006" then "Aftertaste" else if [Like_Attribute] = "LIK_007" then "Shape" else if [Like_Attribute] = "LIK_008" then "Cooling Sensation" else if [Like_Attribute] = "LIK_009" then "Refreshment Sensation" else if [Like_Attribute] = "LIK_012" then "Shape in mouth" else if [Like_Attribute] = "LIK_013" then "Refreshing Sensation" else if [Like_Attribute] = "LIK_015" then "Long lasting flavor" else if [Like_Attribute] = "LIK_016" then "Long lasting freshness" else if [Like_Attribute] = "LIK_017" then "Taste after ... mins" else if [Like_Attribute] = "LIK_018" then "Buttery Taste" else if [Like_Attribute] = "LIK_019" then "Saltiness" else if [Like_Attribute] = "LIK_020" then "Sweetness" else if [Like_Attribute] = "LIK_021" then "Color" else if [Like_Attribute] = "LIK_022" then "Assortment of Colors" else if [Like_Attribute] = "LIK_023" then "Taste of Chocolate" else if [Like_Attribute] = "LIK_024" then "Texture of Chocolate" else if [Like_Attribute] = "LIK_025" then "Filling" else if [Like_Attribute] = "LIK_026" then "Overall Texture of the Filling" else if [Like_Attribute] = "LIK_027" then "COOKIE PIECES IN THE FILLING" else if [Like_Attribute] = "LIK_028" then "Sweetness in Chocolate" else if [Like_Attribute] = "LIK_029" then "REFRESHING THE BREATH" else if [Like_Attribute] = "LIK_030" then "TEXTURE OF THE FIRST FEW CHEWS" else if [Like_Attribute] = "LIK_031" then "Cooling Intensity" else if [Like_Attribute] = "LIK_033" then "Liking Taste after 5 mins" else if [Like_Attribute] = "OL_1ST" then " Overall Liking" else if [Like_Attribute] = "OL_2ND" then " Overall Liking" else null),
   #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Attribute] = "PRODUCT_1ST" or ([Attribute] = "PRODUCT_2ND" and [Liking_Metrics]=" Overall Liking")))
in
    #"Filtered Rows"

yingyinr_0-1663298721261.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-yiruan-msft
Community Support
Community Support

Hi  @Kishore_SR ,

You can apply the filter on the query "Liking Attributes" just as below screeshot:

let
    Source = Excel.Workbook(File.Contents("C:\Users\kishore.mohanraj\Desktop\PowerBI\Mondelez\Copy_MDZPQB TOTAL DATA _5 June 2022.xlsx"), null, true),
    #"DATA FOR Non Time Jobs_Sheet" = Source{[Item="DATA FOR Non Time Jobs",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"DATA FOR Non Time Jobs_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Serial", type text}, {"TEST_ID", type text}, {"AGENCY_ID", Int64.Type}, {"CONSUMER_ID", Int64.Type}, {"PRODUCT", type text}, {"YEAR", Int64.Type}, {"PNUMBER", Int64.Type}, {"CATEGORY", type text}, {"CATEGORY_CODE", type text}, {"BRAND", type text}, {"COUNTRY", type text}, {"PROJECT_TYPE", type text}, {"PROJECT_TITLE", type text}, {"PRODUCT_MONDELEZ", type text}, {"PRODUCT_CODE_MONDELEZ", Int64.Type}, {"PRODUCT_COMPETITOR", type text}, {"PRODUCT_CODE_COMPETITOR", Int64.Type}, {"ACTION_STANDARD", type text}, {"METHOD", type text}, {"VENUE", type text}, {"LOCATIONS", type text}, {"DATE_OF_FIELDWORK", Int64.Type}, {"SAMPLE_SIZE", Int64.Type}, {"SAMPLE", type text}, {"1ST", Int64.Type}, {"PRODUCT_1ST", type text}, {"2ND", Int64.Type}, {"PRODUCT_2ND", type text}, {"OL_1ST", Int64.Type}, {"PI_1ST", Int64.Type}, {"OL_2ND", Int64.Type}, {"PI_2ND", Int64.Type}, {"LIK_002", Int64.Type}, {"LIK_003", Int64.Type}, {"LIK_004", type any}, {"LIK_005", type any}, {"LIK_006", Int64.Type}, {"LIK_007", Int64.Type}, {"LIK_008", Int64.Type}, {"LIK_009", Int64.Type}, {"LIK_012", Int64.Type}, {"LIK_013", type any}, {"LIK_017", Int64.Type}, {"LIK_018", type any}, {"LIK_019", type any}, {"LIK_020", type any}, {"LIK_021", type any}, {"LIK_022", type any}, {"LIK_023", type any}, {"LIK_024", type any}, {"LIK_025", type any}, {"LIK_026", type any}, {"LIK_027", type any}, {"LIK_028", type any}, {"LIK_029", type any}, {"LIK_030", type any}, {"LIK_031", type any}, {"LIK_033", type any}, {"JAR_101", Int64.Type}, {"JAR_102", Int64.Type}, {"JAR_103", type any}, {"JAR_104", type any}, {"JAR_105", type any}, {"JAR_106", type any}, {"JAR_110", type any}, {"JAR_115", type any}, {"JAR_116", type any}, {"JAR_118", Int64.Type}, {"JAR_119", type any}, {"JAR_120", type any}, {"JAR_122", type any}, {"JAR_123", type any}, {"JAR_124", type any}, {"JAR_125", type any}, {"JAR_126", type any}, {"JAR_127", type any}, {"JAR_128", type any}, {"JAR_129", type any}, {"JAR_131", type any}, {"JAR_133", type any}, {"JAR_135", type any}, {"JAR_137", type any}, {"JAR_138", Int64.Type}, {"JAR_139", Int64.Type}, {"JAR_141", Int64.Type}, {"JAR_142", type any}, {"JAR_145", type any}, {"JAR_146", type any}, {"JAR_147", type any}, {"JAR_148", type any}, {"JAR_149", type any}, {"JAR_150", type any}, {"JAR_151", type any}, {"JAR_152", type any}, {"JAR_153", type any}, {"JAR_154", type any}, {"JAR_155", type any}, {"JAR_156", type any}, {"JAR_158", type any}, {"JAR_159", type any}, {"JAR_160", type any}, {"JAR_163", type any}, {"JAR_164", type any}, {"JAR_165", type any}, {"JAR_167", type any}, {"JAR_168", Int64.Type}, {"JAR_169", type any}, {"JAR_170", type any}, {"JAR_174", type any}, {"JAR_175", type any}, {"JAR_178", type any}, {"JAR_179", type any}, {"JAR_180", type any}, {"JAR_182", type any}, {"JAR_183", type any}, {"JAR_184", type any}, {"JAR_185", type any}, {"JAR_186", type any}, {"JAR_187", type any}, {"JAR_188", type any}, {"JAR_190", type any}, {"JAR_195", type any}, {"JAR_196", type any}, {"JAR_205", type any}, {"JAR_208", type any}, {"JAR_209", type any}, {"JAR_210", type any}, {"JAR_211", type any}, {"JAR_213", type any}, {"JAR_214", type any}, {"JAR_216", type any}, {"JAR_217", type any}, {"JAR_218", type any}, {"JAR_219", type any}, {"JAR_220", type any}, {"JAR_222", type any}, {"JAR_223", type any}, {"JAR_224", type any}, {"JAR_225", type any}, {"JAR_226", type any}, {"JAR_227", type any}, {"JAR_230", type any}, {"JAR_231", type any}, {"JAR_234", Int64.Type}, {"JAR_235", type any}, {"JAR_236", type any}, {"JAR_237", type any}, {"JAR_238", type any}, {"JAR_239", type any}, {"JAR_241", type any}, {"JAR_242", type any}, {"JAR_243", type any}, {"JAR_245", type any}, {"JAR_246", type any}, {"JAR_247", type any}, {"JAR_248", type any}, {"JAR_249", type any}, {"JAR_251", type any}, {"JAR_252", type any}, {"JAR_253", type any}, {"JAR_254", type any}, {"JAR_255", type any}, {"JAR_256", type any}, {"JAR_257", type any}, {"JAR_258", type any}, {"JAR_259", type any}, {"JAR_260", type any}, {"JAR_264", type any}, {"JAR_265", type any}, {"JAR_266", type any}, {"JAR_267", type any}, {"JAR_268", type any}, {"JAR_269", type any}, {"JAR_270", type any}, {"INT_001", type any}, {"INT_002", Int64.Type}, {"INT_003", Int64.Type}, {"INT_004", Int64.Type}, {"INT_005", Int64.Type}, {"INT_007", type any}, {"INT_009", type any}, {"INT_012", type any}, {"INT_013", type any}, {"INT_014", type any}, {"INT_015", Int64.Type}, {"INT_016", Int64.Type}, {"INT_017", type any}, {"INT_018", type any}, {"INT_019", type any}, {"INT_020", type any}, {"INT_021", type any}, {"INT_022", type any}, {"INT_023", type any}, {"INT_024", type any}, {"INT_025", type any}, {"INT_026", Int64.Type}, {"INT_027", type any}, {"INT_028", type any}, {"PREF_OVERALL", Int64.Type}, {"PREF_002", Int64.Type}, {"PREF_003", Int64.Type}, {"PREF_004", type any}, {"PREF_005", type any}, {"PREF_006", Int64.Type}, {"PREF_007", Int64.Type}, {"PREF_008", Int64.Type}, {"PREF_009", Int64.Type}, {"PREF_101", type any}, {"PREF_102", type any}, {"PREF_103", type any}, {"PREF_104", type any}, {"PREF_105", type any}, {"PREF_106", type any}, {"PREF_115", type any}, {"PREF_118", type any}, {"PREF_125", type any}, {"PREF_127", type any}, {"PREF_137", type text}, {"PREF_150", type any}, {"PREF_163", type text}, {"PREF_164", type any}, {"PREF_214", type any}, {"PREF_215", Int64.Type}, {"PREF_218", type any}, {"PREF_219", type any}, {"PREF_220", type any}, {"PREF_221", type text}, {"PREF_222", type text}, {"PREF_223", type text}, {"PREF2_OVERALL", type text}, {"PREF2_002", type text}, {"PREF2_003", type text}, {"PREF2_004", type text}, {"PREF2_005", type text}, {"PREF2_006", type text}, {"PREF2_007", type text}, {"PREF2_008", type text}, {"PREF2_009", type text}, {"PREF2_101", type text}, {"PREF2_102", type text}, {"PREF2_103", type text}, {"PREF2_104", type text}, {"PREF2_105", type text}, {"PREF2_106", type text}, {"PREF2_115", type text}, {"PREF2_118", type text}, {"PREF2_125", type text}, {"PREF2_127", type text}, {"PREF2_137", type text}, {"PREF2_150", type text}, {"PREF2_163", type text}, {"PREF2_164", type text}, {"PREF2_214", type text}, {"PREF2_215", type text}, {"PREF2_218", type text}, {"PREF2_219", type text}, {"PREF2_220", type text}, {"PREF2_221", type text}, {"PREF2_222", type text}, {"PREF2_223", type text}, {"BRAND_REC_1", type text}, {"BRAND_1", type text}, {"BRAND_REC_2", type text}, {"BRAND_2", type text}, {"GENDER", type text}, {"AGEGR", type text}, {"FREQCONSUMED_L12", type text}, {"FREQCONSUMED_L12G", type text}, {"BRANDFREQ", type text}, {"BRANDS_REG", type text}, {"BRANDS_MOB", type text}, {"BRANDS_NEVER", type text}, {"MARITALSTATUS", type text}, {"EDUCATION_LEVEL", type text}, {"NUMBER_OF_FAMILY_MEMBERS_IN_HH", type text}, {"CHILDREN_UP_TO_16_YO_IN_HH", type text}, {"WORK", type text}, {"FINANCIAL_SITUATION_IN_HH", type text}, {"LOC", type text}, {"Image URL_M", type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Serial", "PNUMBER", "PRODUCT_CODE_MONDELEZ", "PRODUCT_CODE_COMPETITOR", "VENUE", "LOCATIONS", "DATE_OF_FIELDWORK", "SAMPLE_SIZE", "SAMPLE", "PI_1ST", "PI_2ND", "JAR_101", "JAR_102", "JAR_103", "JAR_104", "JAR_105", "JAR_106", "JAR_110", "JAR_115", "JAR_116", "JAR_118", "JAR_119", "JAR_120", "JAR_122", "JAR_123", "JAR_124", "JAR_125", "JAR_126", "JAR_127", "JAR_128", "JAR_129", "JAR_131", "JAR_133", "JAR_135", "JAR_137", "JAR_138", "JAR_139", "JAR_141", "JAR_142", "JAR_145", "JAR_146", "JAR_147", "JAR_148", "JAR_149", "JAR_150", "JAR_151", "JAR_152", "JAR_153", "JAR_154", "JAR_155", "JAR_156", "JAR_158", "JAR_159", "JAR_160", "JAR_163", "JAR_164", "JAR_165", "JAR_167", "JAR_168", "JAR_169", "JAR_170", "JAR_174", "JAR_175", "JAR_178", "JAR_179", "JAR_180", "JAR_182", "JAR_183", "JAR_184", "JAR_185", "JAR_186", "JAR_187", "JAR_188", "JAR_190", "JAR_195", "JAR_196", "JAR_205", "JAR_208", "JAR_209", "JAR_210", "JAR_211", "JAR_213", "JAR_214", "JAR_216", "JAR_217", "JAR_218", "JAR_219", "JAR_220", "JAR_222", "JAR_223", "JAR_224", "JAR_225", "JAR_226", "JAR_227", "JAR_230", "JAR_231", "JAR_234", "JAR_235", "JAR_236", "JAR_237", "JAR_238", "JAR_239", "JAR_241", "JAR_242", "JAR_243", "JAR_245", "JAR_246", "JAR_247", "JAR_248", "JAR_249", "JAR_251", "JAR_252", "JAR_253", "JAR_254", "JAR_255", "JAR_256", "JAR_257", "JAR_258", "JAR_259", "JAR_260", "JAR_264", "JAR_265", "JAR_266", "JAR_267", "JAR_268", "JAR_269", "JAR_270", "INT_001", "INT_002", "INT_003", "INT_004", "INT_005", "INT_007", "INT_009", "INT_012", "INT_013", "INT_014", "INT_015", "INT_016", "INT_017", "INT_018", "INT_019", "INT_020", "INT_021", "INT_022", "INT_023", "INT_024", "INT_025", "INT_026", "INT_027", "INT_028", "PREF_OVERALL", "PREF_002", "PREF_003", "PREF_004", "PREF_005", "PREF_006", "PREF_007", "PREF_008", "PREF_009", "PREF_101", "PREF_102", "PREF_103", "PREF_104", "PREF_105", "PREF_106", "PREF_115", "PREF_118", "PREF_125", "PREF_127", "PREF_137", "PREF_150", "PREF_163", "PREF_164", "PREF_214", "PREF_215", "PREF_218", "PREF_219", "PREF_220", "PREF_221", "PREF_222", "PREF_223", "PREF2_OVERALL", "PREF2_002", "PREF2_003", "PREF2_004", "PREF2_005", "PREF2_006", "PREF2_007", "PREF2_008", "PREF2_009", "PREF2_101", "PREF2_102", "PREF2_103", "PREF2_104", "PREF2_105", "PREF2_106", "PREF2_115", "PREF2_118", "PREF2_125", "PREF2_127", "PREF2_137", "PREF2_150", "PREF2_163", "PREF2_164", "PREF2_214", "PREF2_215", "PREF2_218", "PREF2_219", "PREF2_220", "PREF2_221", "PREF2_222", "PREF2_223", "Image URL_M", "BRAND_REC_1", "BRAND_1", "BRAND_REC_2", "BRAND_2", "GENDER", "AGEGR", "FREQCONSUMED_L12", "FREQCONSUMED_L12G", "BRANDFREQ", "BRANDS_REG", "BRANDS_MOB", "BRANDS_NEVER", "MARITALSTATUS", "EDUCATION_LEVEL", "NUMBER_OF_FAMILY_MEMBERS_IN_HH", "CHILDREN_UP_TO_16_YO_IN_HH", "WORK", "FINANCIAL_SITUATION_IN_HH", "LOC"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"YEAR", type text}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type1", {"OL_1ST","OL_2ND","LIK_002", "LIK_003", "LIK_004", "LIK_005", "LIK_006", "LIK_007", "LIK_008", "LIK_009", "LIK_012", "LIK_013", "LIK_017", "LIK_018", "LIK_019", "LIK_020", "LIK_021", "LIK_022", "LIK_023", "LIK_024", "LIK_025", "LIK_026", "LIK_027", "LIK_028", "LIK_029", "LIK_030", "LIK_031", "LIK_033"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Only Selected Columns",{{"Attribute", "Like_Attribute"}, {"Value", "Like_Value"}}),
    #"Unpivoted Only Selected Columns1" = Table.Unpivot(#"Renamed Columns", {"PRODUCT_1ST", "PRODUCT_2ND"}, "Attribute", "Value"),
    #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Only Selected Columns1", "Liking_Metrics", each if [Like_Attribute] = "LIK_002" then "Appearence" else if [Like_Attribute] = "LIK_003" then "Flavor / Taste" else if [Like_Attribute] = "LIK_004" then "Texture" else if [Like_Attribute] = "LIK_005" then "Aroma" else if [Like_Attribute] = "LIK_006" then "Aftertaste" else if [Like_Attribute] = "LIK_007" then "Shape" else if [Like_Attribute] = "LIK_008" then "Cooling Sensation" else if [Like_Attribute] = "LIK_009" then "Refreshment Sensation" else if [Like_Attribute] = "LIK_012" then "Shape in mouth" else if [Like_Attribute] = "LIK_013" then "Refreshing Sensation" else if [Like_Attribute] = "LIK_015" then "Long lasting flavor" else if [Like_Attribute] = "LIK_016" then "Long lasting freshness" else if [Like_Attribute] = "LIK_017" then "Taste after ... mins" else if [Like_Attribute] = "LIK_018" then "Buttery Taste" else if [Like_Attribute] = "LIK_019" then "Saltiness" else if [Like_Attribute] = "LIK_020" then "Sweetness" else if [Like_Attribute] = "LIK_021" then "Color" else if [Like_Attribute] = "LIK_022" then "Assortment of Colors" else if [Like_Attribute] = "LIK_023" then "Taste of Chocolate" else if [Like_Attribute] = "LIK_024" then "Texture of Chocolate" else if [Like_Attribute] = "LIK_025" then "Filling" else if [Like_Attribute] = "LIK_026" then "Overall Texture of the Filling" else if [Like_Attribute] = "LIK_027" then "COOKIE PIECES IN THE FILLING" else if [Like_Attribute] = "LIK_028" then "Sweetness in Chocolate" else if [Like_Attribute] = "LIK_029" then "REFRESHING THE BREATH" else if [Like_Attribute] = "LIK_030" then "TEXTURE OF THE FIRST FEW CHEWS" else if [Like_Attribute] = "LIK_031" then "Cooling Intensity" else if [Like_Attribute] = "LIK_033" then "Liking Taste after 5 mins" else if [Like_Attribute] = "OL_1ST" then " Overall Liking" else if [Like_Attribute] = "OL_2ND" then " Overall Liking" else null),
   #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Attribute] = "PRODUCT_1ST" or ([Attribute] = "PRODUCT_2ND" and [Liking_Metrics]=" Overall Liking")))
in
    #"Filtered Rows"

yingyinr_0-1663298721261.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors