Issue in fetching current month and next 11 months without a date field in rows

[GokilaRaviraj]

Hey guys..

I have provided the sample raw data and the expected output here. Please help me with the solutions.

My Input table has Company name, Total Annual amount, Period which is per quarter or per month.

My Expected output should be like, 

company name, period, current month, next month 1..... next month 11

I could fetch the company name, period from my raw data. 

If period is per month then current month, next month 1..... next month 11 should be total annual amount didvide by 12

If period is per quarter then current month, next month 1..... next month 11 should be total annual amount didvide by 4

Whatever value i get here, should be pasted in the current and next 11 months.

I tried to create a table with dates from current date till next 11 months and fetched month names. since my raw data doesn't have any date field, i couldn't make any relationship between them and fetch values.

My biggest doubt here is whether this can be done in power bi or do i need to use sql to change the format of the data and the create reports in PBI

If any one can help me with this issue, it would be really great. 🙂 

[GokilaRaviraj]

Hi.

Now my data looks like below template.

If period is per quarter then my value has to be there only on every 3rd month of the quarter and if it is per year then value must be there only on Jun. 

If you have any idea on this, could you help me with this?

[jgeddes]

Something like the following may work...

let
    // #### Set variables ####
    
    annualShowMonth = 6,
    quarterShowMonth = 3,
    todayDate = Date.From(DateTime.FixedLocalNow()),
    todayMonth = Date.Month(todayDate),
    currentQuarterMonth = Number.Mod(todayMonth, 3),
    monthList = 
    List.Generate(
        ()=>
            todayMonth-1, 
            each _ < todayMonth + 11, 
            each _+1, 
            each Date.MonthName(#date(1899,Number.Mod(_, 12)+1,1))
    ),
    quarterShowList = 
    List.Generate(
        ()=>
            todayMonth - 1,
            each _ < todayMonth + 11,
            each _ + 1,
            each if Number.Mod(Number.Mod(_, 12)+1, 3) = 0 then 0 else null
    ),
    annualShowList = 
    List.Generate(
        ()=>
            todayMonth - 1,
            each _ < todayMonth + 11,
            each _ + 1,
            each if Number.Mod(_, 12)+1 = annualShowMonth then annualShowMonth else null
    ),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRMjQyMDAA0r75eSUZSrE60Uourm5AvpEJRDywNLGoJLUILOPu4QkUMbEwNbUwM9IzNQJyAlKLFJCVeHn7gJSYmJmgG+vr5w8y1sICLA7SF5maCNQUCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Company_Name = _t, Annual_Amount = _t, Period = _t]),
    changeSourceTypes = 
    Table.TransformColumnTypes
    (Source,
        {
            {"Company_Name", type text}, 
            {"Annual_Amount", Int64.Type}, 
            {"Period", type text}
        }
    ),
    addMonthColumns = 
    Table.AddColumn(
        changeSourceTypes, 
        "months", 
        each monthList
    ),
    addPeriodAmountColumn = 
    Table.AddColumn(
        addMonthColumns, 
        "periodAmount", 
        each 
        if [Period] = "Month" 
            then let amount = [Annual_Amount]/12 in List.Repeat({amount}, 12) 
            else 
        if [Period] = "Quarter"
            then let amount = [Annual_Amount]/4 in List.Repeat({amount}, 12)
            else
        if  [Period] = "Per Quarter"
            then let amount = [Annual_Amount]/4 in List.ReplaceValue(quarterShowList, 0, amount, Replacer.ReplaceValue)
            else 
        if [Period] = "Per Year"
            then let amount = [Annual_Amount] in List.ReplaceValue(annualShowList, annualShowMonth, amount, Replacer.ReplaceValue)
            else null
    ),
    addTableColumn = 
    Table.AddColumn(
        addPeriodAmountColumn, 
        "Custom", 
        each Table.FromRows(List.Zip({[months], [periodAmount]}))
    ),
    removeColumns = 
    Table.RemoveColumns(
        addTableColumn,
        {"months", "periodAmount"}
    ),
    promoteNestedHeaders = 
    Table.TransformColumns(
        removeColumns, 
        {{"Custom", each Table.PromoteHeaders(Table.Transpose(_))}}
    ),
    expandNestedTables = 
    Table.ExpandTableColumn(
        promoteNestedHeaders, 
        "Custom", 
        monthList
    )
in
    expandNestedTables

[jgeddes · Accepted solution]

Here is a really rough idea of how you can get this...

to this...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRMjQyMDAA0r75eSUZSrE60Uourm5AvpEJRDywNLGoJLVIKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Company_Name = _t, Annual_Amount = _t, Period = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company_Name", type text}, {"Annual_Amount", Int64.Type}, {"Period", type text}}),
    currentMonth = List.Generate(()=>Date.Month(Date.From(DateTime.FixedLocalNow()))-1, each _ < Date.Month(Date.From(DateTime.FixedLocalNow()))+11, each _+1, each Date.MonthName(#date(1899,Number.Mod(_, 12)+1,1))),
    Custom1 = Table.AddColumn(#"Changed Type", "months", each currentMonth),
    #"Added Custom" = Table.AddColumn(Custom1, "periodAmount", each if [Period] = "Month" then let amount = [Annual_Amount]/12 in List.Repeat({amount}, 12) else let amount = [Annual_Amount]/4 in List.Repeat({amount}, 12)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each Table.FromRows(List.Zip({[months], [periodAmount]}))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"months", "periodAmount"}),
    Custom2 = Table.TransformColumns(#"Removed Columns", {{"Custom", each Table.PromoteHeaders(Table.Transpose(_))}}),
    #"Expanded Custom" = Table.ExpandTableColumn(Custom2, "Custom", currentMonth)
in
    #"Expanded Custom"

Paste the above code into the advanced editor of a blank query so you can review the steps.

[GokilaRaviraj]

Hi @jgeddes 

In the previous post, My logic was to fetch value from current month + 11 months. 

You suggested me this below code to generate this months.

currentMonth = List.Generate(()=>Date.Month(Date.From(DateTime.FixedLocalNow()))-1, each _ < Date.Month(Date.From(DateTime.FixedLocalNow()))+11, each _+1, each Date.MonthName(#date(1899,Number.Mod(_, 12)+1,1))),
Custom1 = Table.AddColumn(#"Added Custom2", "months", each currentMonth)

Now my logic is changed. I need to fetch current month + remaining months of the current year + next year all the months. It should display with month names + year, eg. Sep 2024.

I am trying to write a condition like below,

If currentmonth= september then List should fetch 15 months.

if currentmonth = october then List should fetch 14 months.

But I get error in this step, and after this if i try to unpivot this , it says month value has duplicate. since it has september, october,november, december twice.

can this be acheived by this way ? I tried to acheive this by creating a calendar table, But my issue here is I need to create a table report, I stuck in how to use those calendar table months to display this annual amount values in table report.

Could you please suggest me something to proceed with this logic. 

[jgeddes]

To get a list like...

try the following code...

let
    today = Date.From(DateTime.FixedLocalNow()),
    todayMonth = Date.Month(today),
    startOfCurrentMonth = Date.StartOfMonth(today),
    remainingMonthsThisYear = 12 - todayMonth,
    monthsToGenerate = remainingMonthsThisYear + 12,
    monthList = 
    List.Generate(
        ()=>
            startOfCurrentMonth, 
            each _ <= Date.AddMonths(startOfCurrentMonth, monthsToGenerate), 
            each Date.AddMonths(_, 1), 
            each Text.From(Date.MonthName(_)) & " " & Text.From(Date.Year(_)) 
    )
in
    monthList

[GokilaRaviraj]

Hi,

Currently my logic is to show values from current month + next 11 months

for this i used a M query below:

currentMonth = List.Generate(()=>Date.Month(Date.From(DateTime.FixedLocalNow()))-1, each _ < Date.Month(Date.From(DateTime.FixedLocalNow()))+11, each _+1, each Date.MonthName(#date(1899,Number.Mod(_, 12)+1,1))))

Now the logic is going to be like current month + remaining months of the year + next year all the months

below is the complete m query i used.

let
Source = Excel.Workbook(File.Contents("C:\Users\Gokila\OneDrive - Ledstången\Documents\Reports to load in BI\Rent_Roll_Updated.xlsx"), null, true),
#" Pure Role_Sheet" = Source{[Item=" Pure Role",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#" Pure Role_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Company", type text}, {"Property designation", type text}, {"Building", type text}, {"Agreement number", type text}, {"Tenant (Name)", type text}, {"Tenant Person/Organization no", Int64.Type}, {"Street (Avi)", type text}, {"Postcode (Avi)", Int64.Type}, {"Postal address (Avi)", type text}, {"C/O (Avi)", type text}, {"Customer's reference", type text}, {"Delivery method", type text}, {"Email (Avi)", type text}, {"Bank for private e-invoice", type text}, {"Intermediary", type text}, {"GLN/Peppol", Int64.Type}, {"Direct debit (Yes/No)", type logical}, {"Direct debit provider", type any}, {"Payer number", Int64.Type}, {"AG Clearing", type any}, {"AG account number", type any}, {"Other tenant (Name)", type text}, {"Other tenant Person/organisation no", type text}, {"Contract type", type text}, {"Main agreement", type any}, {"Status", type text}, {"Vacant (Yes/No)", type text}, {"Notification status", type text}, {"Start date", type date}, {"End date", type date}, {"Extended to", type date}, {"Date of extension", type date}, {"Notice period (Mon)", Int64.Type}, {"Extension period (months)", Int64.Type}, {"Warning period", Int64.Type}, {"Termination date", type date}, {"Termination type", type text}, {"Move-out date", type date}, {"Terminated by", type text}, {"Object number", type text}, {"Lgh number", type text}, {"Object type", type text}, {"Object category", type text}, {"Area (m2)", type number}, {"Street (Object)", type text}, {"Postcode (Object)", type text}, {"Postal address (Object)", type text}, {"Period", type text}, {"Deposit", Int64.Type}, {"Basic rent", type number}, {"Base rent (incl. index)", type number}, {"Base rent ((incl. index). (kr/m2))", type number}, {"VAT base rent", type number}, {"Base month", Int64.Type}, {"Index base year", Int64.Type}, {"Regulation percentage", type text}, {"Enumeration month", Int64.Type}, {"Minimum units of change", type text}, {"Allow lowering (Yes/No)", type text}, {"Percentage increase", type text}, {"Minimum Adjustment (%)", type text}, {"Maximum Adjustment (%)", type text}, {"Index amount", type number}, {"Share of property tax (%)", Int64.Type}, {"Taxation type", type text}, {"Property tax", Int64.Type}, {"Multiple Indexed Rows (Yes/No)", type text}, {"IMD (Yes/No)", type any}, {"Electricity", Int64.Type}, {"Electricity (incl. index)", Int64.Type}, {"Water", type number}, {"Water (incl. index)", type number}, {"Hot water", Int64.Type}, {"Hot water (incl. index)", Int64.Type}, {"Cold", Int64.Type}, {"Cooling (incl. index)", Int64.Type}, {"HEAT", Int64.Type}, {"Heat (incl. index)", Int64.Type}, {"Parking", Int64.Type}, {"Parking (incl. index)", Int64.Type}, {"Gas", Int64.Type}, {"Gas (incl. index)", Int64.Type}, {"Water and sewage", Int64.Type}, {"Water and sewage (incl. index)", Int64.Type}, {"Sign", Int64.Type}, {"Sign (incl. index)", Int64.Type}, {"Operation/Maintenance", type number}, {"Operation/Maintenance (incl. index)", type number}, {"Internet/TV", Int64.Type}, {"Internet/TV (incl. index)", Int64.Type}, {"Snow removal", Int64.Type}, {"Snow removal (incl. index)", Int64.Type}, {"Waste disposal", Int64.Type}, {"Waste management (incl. index)", Int64.Type}, {"Cleaning/Cleaning", Int64.Type}, {"Cleaning/Cleaning (incl. index)", Int64.Type}, {"Ventilation", Int64.Type}, {"Ventilation (incl. index)", Int64.Type}, {"Remodeling/tenant adaptation", Int64.Type}, {"Reconstruction/tenant adaptation (incl. index)", Int64.Type}, {"Case article", Int64.Type}, {"Case article (incl. index)", Int64.Type}, {"Other additions", type number}, {"Other supplements (incl. index)", type number}, {"Total additions (incl. index)", type number}, {"Discount year 1 (incl. index)", Int64.Type}, {"Discount year 2 (incl. index)", Int64.Type}, {"Discount year 3 (incl. index)", Int64.Type}, {"Annual discount >= year 4", Int64.Type}, {"Total annual amount", type number}, {"Rental value Contract", type number}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{ "C/O (Avi)", "Customer's reference", "Intermediary", "GLN/Peppol", "Direct debit provider", "AG Clearing", "Payer number", "Other tenant (Name)", "AG account number"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Company","Period","Object number"}, {{"Sum of Annual Amount", each List.Sum([Total annual amount]), type nullable number}}),


#"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Rental Income", each if [Period]="Per quarter" then let amount =[Sum of Annual Amount]/4 in List.Repeat({amount}, 12)
else if [Period]="Per month" then let amount = [Sum of Annual Amount]/12 in List.Repeat({amount}, 12)
else if [Period]="A year" then let amount =[Sum of Annual Amount] in List.Repeat({amount}, 12)
else let amount = [Sum of Annual Amount]/4 in List.Repeat({amount}, 12)),

currentMonth = List.Generate(()=>Date.Month(Date.From(DateTime.FixedLocalNow()))-1, each _ < Date.Month(Date.From(DateTime.FixedLocalNow()))+11, each _+1, each Date.MonthName(#date(1899,Number.Mod(_, 12)+1,1)))),

Custom1 = Table.AddColumn(#"Added Custom2", "months", each currentMonth),

#"Added Custom1" = Table.AddColumn(Custom1, "Custom", each Table.FromRows(List.Zip({[months], [Rental Income]}))),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"months", "Rental Income"}),

Custom2 = Table.TransformColumns(#"Removed Columns1", {{"Custom", each Table.PromoteHeaders(Table.Transpose(_))}}),

#"Expanded Custom" = Table.ExpandTableColumn(Custom2, "Custom", currentMonth),

#"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each true),

#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"augusti", type number}, {"september", type number}, {"oktober", type number}, {"november", type number}, {"december", type number}, {"januari", type number},
{"februari", type number}, {"mars", type number}, {"april", type number}, {"maj", type number}, {"juni", type number}, {"juli", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Rental Income", each [augusti]+[september]+[oktober]+[november]+[december]+[januari]+[februari]+[mars]+[april]+[maj]+[juni]+[juli]),
#"Added Conditional Column" = Table.AddColumn(#"Added Custom", "August", each if [Period] = "Per quarter" then 0 else if [Period] = "A year" then 0 else [augusti]),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "September", each if [Period] = "Per quarter" then [september] else if [Period] = "A year" then 0 else [september]),
#"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "October", each if [Period] = "Per quarter" then 0 else if [Period] = "A year" then 0 else [oktober]),
#"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "November", each if [Period] = "Per quarter" then 0 else if [Period] = "A year" then 0 else [november]),
#"Added Conditional Column4" = Table.AddColumn(#"Added Conditional Column3", "December", each if [Period] = "Per quarter" then [december] else if [Period] = "A year" then 0 else [december]),
#"Added Conditional Column5" = Table.AddColumn(#"Added Conditional Column4", "January", each if [Period] = "Per quarter" then 0 else if [Period] = "A year" then 0 else [januari]),
#"Added Conditional Column6" = Table.AddColumn(#"Added Conditional Column5", "February", each if [Period] = "Per quarter" then 0 else if [Period] = "A year" then 0 else [februari]),
#"Added Conditional Column7" = Table.AddColumn(#"Added Conditional Column6", "March", each if [Period] = "Per quarter" then [mars] else if [Period] = "A year" then 0 else [mars]),
#"Added Conditional Column8" = Table.AddColumn(#"Added Conditional Column7", "April", each if [Period] = "Per quarter" then 0 else if [Period] = "A year" then 0 else [april]),
#"Added Conditional Column9" = Table.AddColumn(#"Added Conditional Column8", "May", each if [Period] = "Per quarter" then 0 else if [Period] = "A year" then 0 else [maj]),
#"Added Conditional Column10" = Table.AddColumn(#"Added Conditional Column9", "June", each if [Period] = "A year" then [juni] else if [Period] = "Per quarter" then [juni] else [juni]),
#"Added Conditional Column11" = Table.AddColumn(#"Added Conditional Column10", "July", each if [Period] = "Per quarter" then 0 else if [Period] = "A year" then 0 else [juli]),
#"Renamed Columns" = Table.RenameColumns(#"Added Conditional Column11",{{"september", "September2"}}),

#"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns",{"augusti", "September2", "oktober", "november", "december", "januari", "februari", "mars", "april", "maj", "juni", "juli"}),
#"Grouped Rows1" = Table.Group(#"Removed Columns2", {"Company", "Period","Object number"}, {{"SeptemberSum", each List.Sum([September]), type number}, {"OctoberSum", each List.Sum([October]), type number}, {"NovemberSum", each List.Sum([November]), type number}, {"DecemberSum", each List.Sum([December]), type number}, {"JanuarySum", each List.Sum([January]), type number}, {"FebruarySum", each List.Sum([February]), type number}, {"MarchSum", each List.Sum([March]), type number}, {"AprilSum", each List.Sum([April]), type number}, {"MaySum", each List.Sum([May]), type number}, {"JunSum", each List.Sum([June]), type number}, {"JulSum", each List.Sum([July]), type number}, {"AugustSum", each List.Sum([August]), type number}, {"Annual Amount", each List.Sum([Sum of Annual Amount]), type nullable number}}),
#"Renamed Columns1" = Table.RenameColumns(#"Grouped Rows1",{{"SeptemberSum", "September"}, {"OctoberSum", "October"}, {"NovemberSum", "November"}, {"DecemberSum", "December"}, {"JanuarySum", "January"}, {"FebruarySum", "February"}, {"MarchSum", "March"}, {"AprilSum", "April"}, {"MaySum", "May"}, {"JunSum", "June"}, {"JulSum", "July"}, {"AugustSum", "August"}}),
#"Cleaned Text" = Table.TransformColumns(#"Renamed Columns1",{{"Company", Text.Clean, type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Cleaned Text",{{"Company", Text.Trim, type text}})
in
#"Trimmed Text"

my output now looks like this.

Could you help  me to write a m query ?