Forum Discussion

DanielPf's avatar
DanielPf
Regular Visitor
1 year ago
Solved

Creating custom table with multiple columns where the columns are not logically related

Hello,

I'm trying to put together a custom table with 12 columns, one column for each month. 

Each column should have a distinct list of account names, where a certain condition is met. I have  a base table let's call it Data that has three columns: Customer Name, Due Date, FY.

 

I want to display the distinct list of customer names in the first column (January), where the Month(Data[Due Date]) = 1,

I want to display the distinct list of customer names in the second column (February), where the Month(Data[Due Date]) = 2, and etc.

 

I am trying it with 

NewTable = CALCULATETABLE(VALUES('Data'[Customer Name]), Month('Data'[Due Date])=1 && ('Data'[FY] = "FY25" ) and it gives me one column with the distinct customer name values for January, but i don't know how to add the rest of the 11 columns for each month.
 
I'm quite new to this, so any help would be appreciated.
Thank you! 🙂



  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi DanielPf 

     

    Please try this:
    Here's the sample table:

    Table:

    In powerquery, remove the [Due Date] and [FY] columns:

    Then click the fx and paste the following code in it:

     

    Table.Group(#"Removed Columns", {"Month(Due Date) - not in data - only for example"}, {{"Count", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}})

     

     

    Expand the Count column:

    Finally, select the Month column and click Pivot Column in the Transform pane:

    Remove the Index Column and Close&Apply.

    The result is as follow:

     

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi DanielPf 

     

    Please try this:
    Here's the sample table:

    Table:

    In powerquery, remove the [Due Date] and [FY] columns:

    Then click the fx and paste the following code in it:

     

    Table.Group(#"Removed Columns", {"Month(Due Date) - not in data - only for example"}, {{"Count", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}})

     

     

    Expand the Count column:

    Finally, select the Month column and click Pivot Column in the Transform pane:

    Remove the Index Column and Close&Apply.

    The result is as follow:

     

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • DanielPf's avatar
      DanielPf
      Regular Visitor
      1 year ago
      Thank you vey much, this actually helped!
  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    1 year ago

    Post some sample data (not a picture) and show your desired result please.

    Can I ask what a row represents in this table? It sounds like each column is unrelated to other columns row-wise and it makes me wonder if what you are trying to do is a good idea.

    • DanielPf's avatar
      DanielPf
      Regular Visitor
      1 year ago

      Hello,

       

      Input:

      Customer NameDue DateMonth(Due Date) - not in data - only for exampleFY
      Customer 101.01.2024JanuaryFY25
      Customer 201.01.2024JanuaryFY25
      Customer 301.01.2024JanuaryFY25
      Customer 401.01.2024JanuaryFY25
      Customer 501.01.2024JanuaryFY25
      Customer 601.01.2024JanuaryFY25
      Customer 701.07.2024JulyFY25
      Customer 801.07.2024JulyFY25
      Customer 901.07.2024JulyFY25
      Customer 1001.10.2024OctFY25
      Customer 101.10.2024OctFY25
      Customer 201.12.2024DecFY25
      Customer 301.12.2024DecFY25
      Customer 401.12.2024DecFY25
      Customer 501.09.2024SeptFY25
      Customer 601.05.2024MayFY25
      Customer 701.05.2024MayFY25
      Customer 801.04.2024AprilFY25
      Customer 901.04.2024AprilFY25
      Customer 1001.12.2024DecemberFY25

       

      Output:

      JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptOctNovDec
      Customer 1  Customer 8Customer 6 Customer 7 Customer 5Customer 10 Customer 2
      Customer 2  Customer 9Customer 7 Customer 8  Customer 1 Customer 3
      Customer 3     Customer 9    Customer 4
      Customer 4          Customer 10
      Customer 5           
      Customer 6           
                  
                  
                  
                  
      Distinct(Customer name) where Month(Due date) = 1          Distinct(Customer name) where Month(Due date) = 12

       

      This output view is used to plan which customer needs attention each month.

       

      Thank you 🙂

  • SachinNandanwar's avatar
    SachinNandanwar
    Icon for Impactful Individual rankImpactful Individual
    1 year ago

    you could probably create a custom table

     

    NewTable =
ADDCOLUMNS( Data,
"Jan","Condition for month of Jan",
"Feb","Condition for month of Feb",

.

.

.
"Dec","Condition for month of Dec")

     

     

    • DanielPf's avatar
      DanielPf
      Regular Visitor
      1 year ago

      I was trying to use add columns like this:
      NewTable = ADDCOLUMNS(Schedule, "January", Filter(DISTINCT(Customer Name), Month(Due Date) = 1), but this gave me an error : A table of multiple values was supplied where a single value was expected.

      • SachinNandanwar's avatar
        SachinNandanwar
        Icon for Impactful Individual rankImpactful Individual
        1 year ago

        You should do something like this

        "January", CALCULATE(VALUES('Data'[Customer Name]), MONTH('Data'[Due Date]) = 1 && 'Data'[FY] = "FY25")