Generate rows at lower level

[pmargari]

Hello all,
Any tips to generetate rows at lower level from a table with data on quarters , just to set the same value of each month related to his quarter  ? (example below) 
I have budgets for salesrep , at quater level, that I want to generate de same amount but at month level , any tips to make this append ? 



Appreciate any tips
All the best
KR

[pmargari · Accepted solution]

Thansk for your reply Nathaniel_C ! , will try this option !! 

[pmargari · Accepted solution]

Hello again 
I think the way I picture my need have created a missunderstood , sorry for that , what I need is  to create rows not columns as this example :

Thanks

[Nathaniel_C]

Hi @pmargari ,
You will not be able to make this append because the column headings are different.  However you could add extra columns for each month based on the quarter values in Power Query. Go to the add column tab, Custom and name the columns by month, and insert the appropriate quarter.  

Apply this table to Power BI and select the quarter, the month columns as in the picture.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[pmargari · Accepted solution]

Hello again 
I think the way I picture my need have created a missunderstood , sorry for that , what I need is  to create rows not columns as this example :

Thanks

[Nathaniel_C · Accepted solution]

Hello @pmargari ,
Like this?

Color = IF(MAX(Sales[Month]) In{"Jan","Feb","Mar"},"yellow", "green") //Add this measure to use in your conditional formatting.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsxJSi0qUdJRMjIAEsYgwhTKitWJVnLOSCzKSS2GSRmbAgkTEGFqCpZ3zEkrSk0BCYKkzUCEiQmEFRsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sales Rep" = _t, Q1 = _t, Q2 = _t, Q3 = _t, Q4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales Rep", type text}, {"Q1", Int64.Type}, {"Q2", Int64.Type}, {"Q3", Int64.Type}, {"Q4", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Jan", each [Q1]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Feb", each [Q1]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Mar", each [Q1]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Apr", each [Q2]),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "May", each [Q2]),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "June", each [Q2]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom5",{{"Jan", Int64.Type}, {"Feb", Int64.Type}, {"Mar", Int64.Type}, {"Apr", Int64.Type}, {"May", Int64.Type}, {"June", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Q1", "Q2", "Q3", "Q4"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Sales Rep"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Month"}, {"Value", "Amount"}}),
    #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "Sort1", each if Text.Contains([Sales Rep], "t") then 1 else if Text.Contains([Sales Rep], "s") then 2 else if Text.Contains([Sales Rep], "d") then 3 else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Sort2", each if [Month] = "Jan" then 1 else if [Month] = "Feb" then 2 else if [Month] = "Mar" then 3 else if [Month] = "Apr" then 4 else if [Month] = "May" then 5 else if [Month] = "June" then 6 else null),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"Sort1", type text}, {"Sort2", type text}})
in
    #"Changed Type2"

Above is the m language to build your table in Power Query.  Then apply that table to Power BI, and add the measure above to conditional formatting. 

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[pmargari · Accepted solution]

Thansk for your reply Nathaniel_C ! , will try this option !!