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

[DanielPf]

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 · Accepted solution]

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]

Thank you vey much, this actually helped!

[SachinNandanwar]

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]

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]

You should do something like this

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

[HotChilli]

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]

Hello,

Input:

Customer Name	Due Date	Month(Due Date) - not in data - only for example	FY
Customer 1	01.01.2024	January	FY25
Customer 2	01.01.2024	January	FY25
Customer 3	01.01.2024	January	FY25
Customer 4	01.01.2024	January	FY25
Customer 5	01.01.2024	January	FY25
Customer 6	01.01.2024	January	FY25
Customer 7	01.07.2024	July	FY25
Customer 8	01.07.2024	July	FY25
Customer 9	01.07.2024	July	FY25
Customer 10	01.10.2024	Oct	FY25
Customer 1	01.10.2024	Oct	FY25
Customer 2	01.12.2024	Dec	FY25
Customer 3	01.12.2024	Dec	FY25
Customer 4	01.12.2024	Dec	FY25
Customer 5	01.09.2024	Sept	FY25
Customer 6	01.05.2024	May	FY25
Customer 7	01.05.2024	May	FY25
Customer 8	01.04.2024	April	FY25
Customer 9	01.04.2024	April	FY25
Customer 10	01.12.2024	December	FY25

Output:

January	February	March	April	May	June	July	August	Sept	Oct	Nov	Dec
Customer 1			Customer 8	Customer 6		Customer 7		Customer 5	Customer 10		Customer 2
Customer 2			Customer 9	Customer 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 🙂