Specific power query transformation

Power Query
1

Hi,
I’m wondering if is possible to be done following transformation in power query. I’ve attached an excel file with two tables. Table on the left is the input, where table on the right is the output needed to be transformed.
Output transformation should be as following:
Whenever we have rows with duration longer than 1 month than Total Price should be divided by the number of the Month Duration and the LastBilledDateUtc column should be filled down incrementally with the following months.

For example row 9 in my example file have Total Price of 1532 and Duration of 12 Months, when we divide this, it needs to insert 12 rows bellow instead of this record, and the values for Duration column always should be 1, Total Price will be the value of the division 1532/12 and LastBilledDateUtc column will increment for each row (basically the value for the next month)
Rest of the column will have duplicate/same values.

Experts I wonder if this is possible to be done as transformation.

pq transformation.xlsx (32.1 KB)

1 Like
2

@boshevski
pq transformation.xlsx (42.9 KB)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rVZLkmM5DrtLrZuZokiJ5FkqeqEf73+EgZzOLO+qHTELO/wcehIEggB///5VtKnbWcQzgjRnkrNvKlaU+25HV/z655d7XUOb0FFtpIsHjZGHwi0i+jk1D5aldGvJnXibkBo38lGN6qmBd0RtVCzj+3l81c/SPmup5fHPz8O///z+pUtn6qw0hxRSV5wWR2ifkHFa11x2j7TTeWinOAClawnF2IO2Woyu0pk7lo3aAr8K7TjYbbZGs3Un44ED17Ao38guluKftV8sjz/s5+ECk3bYV9uUgfspTwEXJWhX4aM7Gq+8Jx7zc1ajXURJA5hmb0FZ+6g5clW++A9n92hJbAP4eWyQGk57bzaVPCz2TVl7IiuvyMofZLOxLpSP1uJJeuSQJ7a25R7d69THWzkLT3aUuwiT9qHkExzHsix71cUmWGa9rJMcVApKqP1M8i5BfMqReUrIOk9ktZUPvXjis+gPOP95eNSzCUNpShsvk+oEH7UzCjVSpoFMuczbNB8OKja3ikLxBjhQbF1OmN4a7kvbkrRYmzqUCnWA2Ol8qK2ae9cRO+SbtqIf1h4/Pqv/oIufh4suwqfFnrQC8kW1GujAjU/HbVN8ZR33oplRwDFVkfqkTkF49eO7NazTeK+oNeIj4gsdywu674eLLn0HBy56+HLXoV6QoiQyRX2E2X5w58JzlU4OIZDKGRRWIdS1y+Ic4u5vFrbb3wsb6X1lh7JrklYxcHI6zel75jgo5z31DDsWNqjaBrii10J6I/SyF7Mzssw3LOTx1aR+lAc+eAf/4Pt+eBpJH4b3JuUe2HHDSGZNobrnymH7nH0387J1rzapOwhRYKK5+6KWsMC+PIvP98jj8peywigkuAg1FRwJQaNfh1PKqAr5ic8viYxWGgqP9nFYHNZGdkj1jAZ0HUp4tyW+2yFe2yH+IKu5uHT44+6OvQb2GnN1aj5MILlT7V5OPW1xK9QtF4rVnMbuRh0y1LH28Fbf9Dh9RsFXc5ZXnF/lzNzW7xZ876r7sZkxzRPKEYqa3jMNDDkHermDVl1YESDhBslqtTSej2XvJNaTtPJazvJSztH7gcNWWBoEoQZpjOv9EdZi9zKjXa3ObGXEbJTswDNvjJYrKEgzYY4so/93ZHepmHzI7THkZ/mJrZun/Q9vs6zmjFwASgiXofRAcxK0vVaV5TKvwwFBasA0+GzQy1lgNePQmq1sRu+iyd/kTeSj29M3XkO1vIRqG1HdkeBVDcaanoSUEghLGcZaZ3006SkVPQZzRnLgErYQInr4xonOsmWDzvda4Utv9ccxvnh7sQ9fu56F+vQaiHuMJcjxWjAw1XlBj11v97UlDgViPtkDLog+JnQJJD/bypWn7UeF3kkG/wsyZpCRmGmaMY5sSPSoa9K+lWJVRwBcY8NktFfvVJrgyGvNgxGr2TAszY4ZaVzHQkfXbbzIMQJAlgW9PDHxleGCzBvZenki82egXjuzl1T4fvgKVLlzxCD4JupTIJWQDpWPzg1To/e4dhp1SE5Uu85yA9WxrIVR5jotMCIh0f6ftP37Pw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SubscriptionId = _t, TenantId = _t, UserId = _t, Duration = _t, TotalPrice = _t, NextBillingDateUtc = _t, LastBilledDateUtc = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"SubscriptionId", type text}, {"TenantId", type text}, {"UserId", type text}, {"Duration", Int64.Type}, {"TotalPrice", type number}, {"NextBillingDateUtc", type date}, {"LastBilledDateUtc", type date}}),
    Result = Table.AddColumn ( 
            ChangedType, 
            "Custom", each 
            let
                CurrentRow = _,
                CurrentRowList = {CurrentRow},
                RepeatCurrentRow = List.Repeat ( CurrentRowList, CurrentRow[Duration] ),
                ListToTable = Table.FromRecords ( RepeatCurrentRow ),
                RowCount = Table.RowCount ( ListToTable ),
                Adjustment = 
                    if RowCount > 1 then
                        let
                            AddIndex = Table.AddIndexColumn ( ListToTable, "Index", 0, 1 ),
                            IncrementMonth = Table.AddColumn ( AddIndex, "Next Month", each Date.AddMonths ( [LastBilledDateUtc], [Index] ) ),
                            AdjustDuration = Table.AddColumn ( IncrementMonth, "New Duration", each 1 ),
                            AdjustPrice = Table.AddColumn ( AdjustDuration, "PriceAdjustment", each [TotalPrice] / RowCount ),
                            RemoveCols = Table.RemoveColumns ( AdjustPrice, {"Index", "Duration", "TotalPrice", "LastBilledDateUtc" }),
                            RenameCols = Table.RenameColumns ( RemoveCols, {{"Next Month", "LastBilledDateUtc"}, {"New Duration", "Duration"},{"PriceAdjustment", "TotalPrice"}}),
                            ReorderCols = Table.ReorderColumns ( RenameCols,{"SubscriptionId", "UserId", "TenantId", "Duration", "TotalPrice", "NextBillingDateUtc", "LastBilledDateUtc"})
                        in
                            ReorderCols
                    else ListToTable
            in
                Adjustment
    ),
    RemovedOtherCols = Table.SelectColumns ( Result, { "Custom" } ),
    ExpandedCustom = Table.ExpandTableColumn ( 
            RemovedOtherCols, "Custom", 
            {"SubscriptionId", "TenantId", "UserId", "Duration", "TotalPrice", "NextBillingDateUtc", "LastBilledDateUtc"}, 
            {"SubscriptionId", "TenantId", "UserId", "Duration", "TotalPrice", "NextBillingDateUtc", "LastBilledDateUtc"}
    ),
    ChangedType2 = Table.TransformColumnTypes(ExpandedCustom,{{"SubscriptionId", type text}, {"TenantId", type text}, {"UserId", type text}, {"Duration", Int64.Type}, {"TotalPrice", type number}, {"NextBillingDateUtc", type date}, {"LastBilledDateUtc", type date}})
in
    ChangedType2

image
image1326×750 95.1 KB

3 Likes
3

@AntrikshSharma works like a charm, thanks a ton. Any recommendation on how to progress on the learning path to M code to achieve level like this ? because this is not an easy solution that you provided.

1 Like
4

@boshevski Awesome!

You can getter better in M(or literally anything) by solving someone else’s problem not just here only but anywhere, Power BI Community forum, Stack Overflow, Reddit, are flodded with DAX, Power Query, M questions everyday.

Also observe the code that is generated by each Power Query transformation step and then open new file in another window and write code yourself by just copying, and once you build muscle memory and make mistake you will finally start to grab hold of how everything works

One can learn more by following those who know more than them, @Melissa solves way more question with M(Not just UI) than anyone here, and has a Beginners course as well as a practical course, that you should definitely watch.

Other notable people in this field according to me are

Ben Gribaudo (He has a complete series on M)- https://bengribaudo.com/blog/2017/11/17/4107/power-query-m-primer-part1-introduction-simple-expressions-let

Chris Webb - https://blog.crossjoin.co.uk/

Unlike DAX hit & trial works pefectly in M so you can do that too.

There is Accelerator series on EDNA Platform where you will find many Power Query challenges.

You can also get books on Amazon, but personally speaking I find Power Query books boring as mostly they cover UI related stuff so I chose the hit & trial way of learning.

Most importantly being an Expert in Power Query/M is not absolutely necessary, it is better to be an Expert in SQL/Python/Data Modeling/Data Visualization because Power Query is a proprietary tool and I don’t like the fact that it limits your expertise to just Miscrosoft’s tools. So you should aim to be Intermediate user of M and more advanced user of non-proprietary tools. So don’t burden yourself :+1:

1 Like
5

@AntrikshSharma thanks for your extensive answer, also for the resources provided and the guidance. For me M is area I need to get better at creating custom solutions which are not part of the standard provided transformation. I agree intermediate knowledge will be sufficient for M in most cases. On the other hand I’m expert in MS SQL and lately started a journey with Python for web scraping. Also i’m trying to get better at visualization part to make more appealing reports, not just to be functional reports.
Thanks again.

2 Likes