Forum Discussion

derekli1700's avatar
derekli1700
Icon for Helper III rankHelper III
1 year ago
Solved

Need help making a Filter for a Matrix after Unpivoting?

Hi, I'm trying to make a filter for my matrix table but i'm running into a problem with duplicate values when unpivoting / unsure if there's a easier way without unpivoting   My (simplified) data T...
Need Help
Tutorial Requests
  • v-karpurapud's avatar
    v-karpurapud
    1 year ago

    Hi derekli1700 
    Thank you for reaching out to the Microsoft Community Forum.

    I have attached the PBIX file. Could you please check if it meets your expectations? If not, could you share the details? Please let me know.

     

    If I answered your question, kindly mark it as "Accepted Solution" so that it will help others find it easily.

    Thank you.

     
     

Cookistador's avatar
Cookistador
Icon for Super User rankSuper User
1 year ago

Hello derekli1700 

 

Is it what you need?

If it is the case, this is the transformation I did in Power Query:

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxx\OneDrive\Documents\Power BI Report.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Period", type date}, {"Averaged value", type number}, {"Vol (L)", Int64.Type}, {"PRODUCT 1", Int64.Type}, {"PRODUCT 2", Int64.Type}, {"PRODUCT 3", Int64.Type}, {"PRODUCT 4", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Period", "Averaged value", "Vol (L)"}, "Attribute", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Columns",{{"Period", Order.Ascending}})
in
    #"Sorted Rows"

 

If you prefer to do it manually,
Select the product columns PRODUCT1,PRODUCT2,PRODUCT3,PRODUCT4 and select Unpivot columns

 

27 values are present for january 24 (in your excel sheet) and I got 27 lines in Power BI

 

Do not hesistate to ask if something is missing, or if it is not what you are trying to achieve

  • derekli1700's avatar
    derekli1700
    Icon for Helper III rankHelper III
    1 year ago

    Hi - i typed that into advanced editor/did it manually and duplicate values still show. Going back to looking at the excel sheet - do you think there's an easier way to have a PRODUCT slicer visual for the matrix table i posted? I just want a slicer visual for PRODUCT that can interact with the table but i dont know if it can be done with the way my excel table is set up, if that makes sense