cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

## Dynamic column creation

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.

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.

1 ACCEPTED SOLUTION
Community Support

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

10 REPLIES 10
Community Support

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

Frequent Visitor

Thank you so much! very helpful! 🙂

Anonymous
Not applicable

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?

Resolver IV

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"``````
Resolver IV

I assume you need this result:

You need 3 simple queries:

``= List.Distinct(Transactions[#"Company Name"])``
``= #table(Query1,{})``
``= StartEnd&Query2``

Good Luck,

//JW

Super User

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:

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!

Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Community Support

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

Frequent Visitor

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

Resident Rockstar

Can you provide some sample data with Expected results?

Frequent Visitor

Hi, Please refer to the above edited query.