Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello, everyone!
I have the following table structure.
Site Name | Account Number | 12/1/2018 | 11/1/2018 | 10/1/2018 | 9/1/2018 | 8/1/2018 | 7/1/2018 | 6/1/2018 | 5/1/2018 | 4/1/2018 | 3/1/2018 | 2/1/2018 | 1/1/2018 | 12/1/2017 | 11/1/2017 | 10/1/2017 | 9/1/2017 | 8/1/2017 |
Name | 12 | 50 | 50 | 50 | 50 | |||||||||||||
Name 2 | 123 | 3919 | 3919 | 3332 | 50 | 50 | 3919 | 3332 | 50 | 50 | ||||||||
Name 3 | 123 | 3919 | 3919 | 3332 | 3919 | 3919 |
3332 |
What I wanto to do is create a new column with the average for the values that are in columns with same Year (Red values on table above that belong to the same line, for example).
I´ve tried this.
1 - Create a function that returns the columns with , for example, 2018 as year
(Table)=> let Custom1 = Record.ToTable(Table{0}), #"Removed Columns" = Table.RemoveColumns(Custom1,{"Value"}), #"Removed Top Rows" = Table.Skip(#"Removed Columns",2), #"Added Custom" = Table.AddColumn(#"Removed Top Rows", "Ano", each Text.End([Name],4)), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Ano] = "2018")), #"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows",{"Ano"}), Custom2 = Table.ToList(#"Removed Columns2") in Custom2
2 - Tried to create the new column using the list names above as reference.
= Table.AddColumn(Table, "Average", each List.Average(ListarAno2018(Table)), Currency.Type)
I think that I´m close to get it, but the code on step 2 doesn´t accept the list as reference.
Thank you all so much for any help!
Hi @Anonymous,
I have a little confused about your requirement.
If it is convenient, could you share your desired output based on your sample data so that we can understand your scenario better.
Best Regards,
Cherry
Hello, @v-piga-msft!
Here´s the output that I´m looking for..(Image below)
One more thing, the new query must calculate the average regardless the number of columns with a specific year date. Therefore, it must calculate even if the number of columns with "2018" or "2017" be different of 2 as the example illustrates.
Thank you!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
88 | |
82 | |
65 | |
64 | |
58 |
User | Count |
---|---|
171 | |
113 | |
110 | |
73 | |
73 |