Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Independent Column Values for Pivot Table

Hi All,   I have an excel file that has 108 code columns like A6A** and A6K**.  I would like to count rows for every single values in excel. However, when I do it with Power BI Pivot Table, it auto...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • bolfri's avatar
    bolfri
    3 years ago

    Ok. Then... In Power Query M:

    let
    Źródło = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc6xCoYwDATgd+nsYlrdU3pm+KekhQzF938NSxX5cQg33MeR3gNc2zyuTjGtYQm8/5hn8shz6UHh5jCyWmQYelC+UcaNxBQmQFO0FLf1o/JUUqoMIJEobjgQ04eVZ+zw0bqaOuhFf2+dFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Long VIN" = _t, A6K = _t, A6A = _t]),
    #"Unpivot Other Columns" = Table.UnpivotOtherColumns(Źródło, {"Long VIN"}, "Family", "Value")
in
    #"Unpivot Other Columns"

     

    Create New Measure:

    Count of VIN = DISTINCTCOUNT('Sample'[Long VIN])

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

The sample data like below. Correct, one long VIN value has 108 code families like A6K and A6A. 

 

Long VIN A6K A6A
EWQTEWQTEASW2341 A6KAA A6AAA
QEWRWER2RSDG3412 A6KAB A6ABE
QGRQERGEETQET4351 A6KAB A6ABB
GDSGTQEG32235EFE34 A6KAB A6ABD
QGFWEFEWQRQWE234 A6KAA A6AAA

 

  • bolfri's avatar
    bolfri
    Solution Sage
    3 years ago

    So based on this sample data that you have here:

    Long VINA6KA6A
    EWQTEWQTEASW2341A6KAAA6AAA
    QEWRWER2RSDG3412A6KABA6ABE
    QGRQERGEETQET4351
    		A6KABA6ABB
    GDSGTQEG32235EFE34
    		A6KABA6ABD
    QGFWEFEWQRQWE234
    		A6KAAA6AAA

     

    What do you want to count? Based on this 5 rows?

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      I want to count specific values for family columns. For example, when I want to get the counts for   A6KAB from A6K column and A6AAA from A6A column, the table should be like below.  I don't want to get results only for both A6KAB and A6AAA values. I would like to count both values seperately.

       

       

      • bolfri's avatar
        bolfri
        Solution Sage
        3 years ago

        Ok. Then... In Power Query M:

        let
    Źródło = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc6xCoYwDATgd+nsYlrdU3pm+KekhQzF938NSxX5cQg33MeR3gNc2zyuTjGtYQm8/5hn8shz6UHh5jCyWmQYelC+UcaNxBQmQFO0FLf1o/JUUqoMIJEobjgQ04eVZ+zw0bqaOuhFf2+dFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Long VIN" = _t, A6K = _t, A6A = _t]),
    #"Unpivot Other Columns" = Table.UnpivotOtherColumns(Źródło, {"Long VIN"}, "Family", "Value")
in
    #"Unpivot Other Columns"

         

        Create New Measure:

        Count of VIN = DISTINCTCOUNT('Sample'[Long VIN])