Forum Discussion

dwedding3's avatar
dwedding3
Frequent Visitor
3 years ago
Solved

Weird Table Question

Hello yall!   I'm new to Powerbi, and I'm having an issue with a measure I want to make.   I have an excel table that gets data from a formstack, that has a column that basically contains a few o...
Need Help
  • AbhinavJoshi's avatar
    AbhinavJoshi
    3 years ago

    Hello dwedding3. You can achieve this Power Query Editor. Please find the full source code. I added a conditional column, it checks if your "colour column" contains "Other", it outputs other, else the column value that it contains before. Then I added index to calculate the count of colors as I have no Unique ID.

     


     let
    Source = Excel.Workbook(File.Contents("C:\Users\AJoshi\Downloads\Random.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
    #"Added Conditional Column" = Table.AddColumn(#"Removed Top Rows", "Color", each if Text.Contains([Column1], "Other") then "Other" else [Column1]),
    #"Renamed Columns" = Table.RenameColumns(#"Added Conditional Column",{{"Column1", "Pre_Color"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1, Int64.Type)
    in
    #"Added Index"


    I hope this helps! 

AbhinavJoshi's avatar
AbhinavJoshi
Responsive Resident
3 years ago

Hello dwedding3. Just to understand the question correctly, you want all the others colours counted as one value?. As per your example, let's say you have Red, Red, Blue, Other Pink, Other: Yellow. The count should be 2 Red, 1 Blue, and 2 others. Let me know if I understand this correctly.

 

  • dwedding3's avatar
    dwedding3
    Frequent Visitor
    3 years ago

    Yep! exactly that!

    • AbhinavJoshi's avatar
      AbhinavJoshi
      Responsive Resident
      3 years ago

      Hello dwedding3. You can achieve this Power Query Editor. Please find the full source code. I added a conditional column, it checks if your "colour column" contains "Other", it outputs other, else the column value that it contains before. Then I added index to calculate the count of colors as I have no Unique ID.

       


       let
      Source = Excel.Workbook(File.Contents("C:\Users\AJoshi\Downloads\Random.xlsx"), null, true),
      Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
      #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}}),
      #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
      #"Added Conditional Column" = Table.AddColumn(#"Removed Top Rows", "Color", each if Text.Contains([Column1], "Other") then "Other" else [Column1]),
      #"Renamed Columns" = Table.RenameColumns(#"Added Conditional Column",{{"Column1", "Pre_Color"}}),
      #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1, Int64.Type)
      in
      #"Added Index"


      I hope this helps!