cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kavitha10
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.

 

Thanks in advance!

 

Edited:

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

For example, let’s consider

Kavitha10_0-1618310992613.png

Kavitha10_1-1618311040112.png

Kavitha10_2-1618311057368.png

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
v-kelly-msft
Community Support
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:

v-kelly-msft_0-1618365116579.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

View solution in original post

10 REPLIES 10
v-kelly-msft
Community Support
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:

v-kelly-msft_0-1618365116579.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

Thank you so much! very helpful! 🙂

Rocco_sprmnt21
Community Champion
Community Champion

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
Resolver III
Resolver III

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

Capture2.PNG

 

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
Resolver III
Resolver III

I assume you need this result:

Capture1.PNG

 

You need 3 simple queries:

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

Good Luck,

//JW

ERD
Memorable Member
Memorable Member

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:

ERD_1-1618325023404.png

 

ERD_0-1618325003812.png

 

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

v-kelly-msft
Community Support
Community Support

Hi  @Kavitha10 ,

 

Do you wanna get something as below:

v-kelly-msft_0-1618307738658.png

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!

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

Vera_33
Super User
Super User

Hi @Kavitha10 

 

Can you provide some sample data with Expected results?

Hi, Please refer to the above edited query.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors