Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, this is my table.
Region | Product | Sub-product | Attribute | Value |
Czech Republic | Furniture | Chair | Q1 | 12899 |
Czech Republic | Furniture | Chair | Q2 | 15929 |
Czech Republic | Furniture | Chair | Q3 | 12123 |
Czech Republic | Furniture | Chair | Q4 | 12093 |
How do I convert from this table to this one? Splitting quaters into 3, there should be month column.
Region | Product | Sub-product | Attribute | Value | Month/Date |
Czech Republic | Furniture | Chair | Q1 | 4299.7 | 2022.01.01 |
Czech Republic | Furniture | Chair | Q1 | 4299.7 | 2022.02.01 |
Czech Republic | Furniture | Chair | Q1 | 4299.7 | 2022.03.01 |
Czech Republic | Furniture | Chair | Q2 | 5309.7 | 2022.04.01 |
Czech Republic | Furniture | Chair | Q2 | 5309.7 | 2022.05.01 |
Czech Republic | Furniture | Chair | Q2 | 5309.7 | 2022.06.01 |
and so on. I have different countries, products and subproduct as well.
How do I achieve this on Power Query?
Thanks for your support.
Solved! Go to Solution.
Hi @Anonymous ,
you can extract the number part from the quarter-column and then create a list of months based on that.
Expand that list out and divide by 3 to get monthly values:
// Table1
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Region ", type text}, {"Product", type text}, {"Sub-product", type text}, {"Attribute", type text}, {"Value", Int64.Type}}),
InsertBase = Table.AddColumn(#"Changed Type", "Base", each Number.From(Text.AfterDelimiter([Attribute], "Q")), type number),
#"Added Custom" = Table.AddColumn(InsertBase, "Months", each {([Base]*3-2) .. [Base]*3}),
#"Expanded Months" = Table.ExpandListColumn(#"Added Custom", "Months"),
#"Added Custom1" = Table.AddColumn(#"Expanded Months", "MonthlyValue", each [Value]/3)
in
#"Added Custom1"
Please also check file enclosed.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @Anonymous ,
you can extract the number part from the quarter-column and then create a list of months based on that.
Expand that list out and divide by 3 to get monthly values:
// Table1
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Region ", type text}, {"Product", type text}, {"Sub-product", type text}, {"Attribute", type text}, {"Value", Int64.Type}}),
InsertBase = Table.AddColumn(#"Changed Type", "Base", each Number.From(Text.AfterDelimiter([Attribute], "Q")), type number),
#"Added Custom" = Table.AddColumn(InsertBase, "Months", each {([Base]*3-2) .. [Base]*3}),
#"Expanded Months" = Table.ExpandListColumn(#"Added Custom", "Months"),
#"Added Custom1" = Table.AddColumn(#"Expanded Months", "MonthlyValue", each [Value]/3)
in
#"Added Custom1"
Please also check file enclosed.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.