Forum Discussion
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.
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.
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,
KellyDid I answer your question? Mark my post as a solution!
10 Replies
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,
KellyDid I answer your question? Mark my post as a solution!
Thank you so much! very helpful! 🙂
Hi, Please refer to the above edited query.
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,
KellyDid I answer your question? Mark my post as a solution!
Hi, Thank you for the response. Please refer to the above edited query. I have elaborated my requirement.
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!
I assume you need this result:
You need 3 simple queries:
= List.Distinct(Transactions[#"Company Name"])= #table(Query1,{})= StartEnd&Query2Good Luck,
//JW
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"
