Dynamic column creation

[Kavitha10]

Hi All,

Is there any way to dynamically create calculated columns for each of the values stored in the list/table.

I have a list/table with values A,B,C, I need to create columns - A with filter on A, B with filter on B and so on dynamically.

Any help is really appreciated.

Thanks in advance!

Edited:

Please refer to the example below for further clarification on my query.

For example, let’s consider

If Date in Table-1 falls between Start Date and End Date of Table-2, then take sum of Amount from Table-1. So, I need to dynamically create columns for each of the distinct values of Company Name from Table-1 (as shown in Result of Table-2 to be) using M query.

[v-kelly-msft · Accepted solution]

Hi  @Kavitha10 ,

You could do it using dax expression:

Measure = 
CALCULATE(SUM(Table1[Amount]),FILTER(ALL(Table1),'Table1'[Date]>=MAX('Table2'[Start Date])&&'Table1'[Date]<=MAX('Table2'[End Date])&&'Table1'[Company Name]=MAX('Table1'[Company Name])))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Kavitha10]

Thank you so much! very helpful! 🙂

[Anonymous]

A clarification: but the dates in [start] and [end] are only by chance the beginning and the end of the month or is it always like this?

Could you upload in somewhere a file (excel?) containing the examples table?

[JW_van_Holst]

In additon to my earlier response. Completely solving the problem in M is more easy than constructing the extended table with empty columns.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLUNdQ1MjAyBDENDJRidaDCFrpGMHEjJHEjY4S4MVTcCaTeFGGOCZK4ua4xTNgUKuwMMsYIYYwZSDwWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Company Name" = _t, Date = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company Name", type text}, {"Date", type date}, {"Amount", Int64.Type}}),
    #"Inserted Start of Month" = Table.AddColumn(#"Changed Type", "Start of Month", each Date.StartOfMonth([Date]), type date),
    #"Inserted End of Month" = Table.AddColumn(#"Inserted Start of Month", "End of Month", each Date.EndOfMonth([Date]), type date),
    #"Grouped Rows" = Table.Group(#"Inserted End of Month", {"Company Name", "Start of Month", "End of Month"}, {{"Total Amount", each List.Sum([Amount]), type nullable number}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[#"Company Name"]), "Company Name", "Total Amount", List.Sum),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"A", "B", "C"})
in
    #"Replaced Value"

[JW_van_Holst]

I assume you need this result:

You need 3 simple queries:

= List.Distinct(Transactions[#"Company Name"])

= #table(Query1,{})

= StartEnd&Query2

Good Luck,

//JW

[ERD]

Hi @Kavitha10 ,

I don't know all your prerequisites but just in case: are you sure you need to add anything in M?

As far as I can see from your example, your second table consists of start date and end date of each month. Thus the result can be achieved by:

1. Creating a proper Calendar table with columns for start date and end date.

2. Creating relation between Fact table (Date column) and Calendar table (Date column).

3. Build matrix:

Did I answer your question? Mark my post as solution!

[v-kelly-msft]

Hi  @Kavitha10 ,

Do you wanna get something as below:

If so,create a measure as below:

Measure = CONCATENATEX(ALLSELECTED('Table'[Value]),[Value],",")

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Kavitha10]

Hi, Thank you for the response. Please refer to the above edited query. I have elaborated my requirement.

[Vera_33]

Hi @Kavitha10 

Can you provide some sample data with Expected results?

[Kavitha10]

Hi, Please refer to the above edited query.