How to use M code to analyze a dataset and put more new rows into it, depending of its current data?

[xrodpim]

Hi everyone !

I´m a beginner with Power Query and I have studied about M code. I saw how to transform my dataset and put new rows into it. For example, I can do something like this:

= Table.InsertRows( #"New Rows", 0, {[#"Date"= "01/01/1979", #"Item"="LTE", #"Built"="100", ]} )

My dataset has several kinds of itens and many rows. But a simple example is like this:

Date	Item	built
10/10/2021	GSM	100%
11/10/2021	LTE	100%
12/10/2021	TDMA	100%
15/01/2022	LTE	100%
17/01/2022	TDMA	100%

In the table above, the GSM Item is absent for January/2022. In this case I have to put one more row in the table, with values ​​like this:

01/01/2022

GSM

-3

This idea is the transformation I need to implement. However, I still can't figure out how to read my entire dataset, parsing it, and deciding to use Table.InsertRows.

How can I use M code to figure out what itens is not present for wich months? I´m guessing I have to do some kind of loop in my code to read all rows of my dataset and determine what itens is not present for what months. But, can I codify such logics in M code? Should I use advanved editor? I even don´t know yet if such logic must be placed between a 'let 'and a 'in'.

Can someone here give me some idea about it , to clarify it ? My question is mainly about M code example. How to codify it?

Best regards.

Rodrigo Pimenta Carvalho

[ronrsnfld · Accepted solution]

• Create a list of all the Items
• Group By "Year-Month"
  • Examine each subtable to create a list of the missing Items
• Use List.Accumulate to create a table of the missing items
• Combine that table with the original subtable.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQByIjAyNDJR0l92BfIGloYKCqFKsDlDNEkvMJcUWRM0KSC3HxdUSRNNU3MARJGmHRaI4kh6IxFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Item = _t, built = _t]),
    typeIt = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Item", type text}, {"built", Percentage.Type}}, "en-GB"),

//added a row in a new month with only a single Item for testing 
//  when multiple items are missing from a month
    #"Previous Step" = Table.InsertRows(typeIt,Table.RowCount(typeIt),{[Date=#date(2022,3,1), Item="GSM", built=1]}),

//Replace above steps with however you actually set up your data

    #"All Items" = List.Distinct(#"Previous Step"[Item]),

//Group by month-year
// generate a list of missing items in each month
// as well as the original sub-table
    #"Add Year-Month Column" = Table.AddColumn(#"Previous Step", "Year-Month", each Date.ToText([Date], Date.ToText([Date],"yyyy-MM")),type text),
    #"Group by Year-Month" = Table.Group(#"Add Year-Month Column","Year-Month",{
        {"missing",
        (t)=> List.RemoveNulls(
        List.Generate(
            ()=>[pos=List.PositionOf(t[Item],#"All Items"{0}), idx=0],
            each [idx] < List.Count(#"All Items"),
            each [pos=List.PositionOf(t[Item],#"All Items"{[idx]+1}), idx=[idx]+1],
            each if [pos] = -1 then #"All Items"{[idx]} else null)
        )},
        {"All", each _, type table[Date=date, Item=text, built=Percentage.Type]}
    }),

//add custom column with the added rows for each month
    #"Added Custom" = Table.AddColumn(#"Group by Year-Month", "Added Rows", 
        each Table.Combine({
            [All],
            Table.FromRecords(
                List.Accumulate([missing], {}, (state, current)=> state & 
                    {[Date=Date.From([#"Year-Month"]), Item=current, built=-3]})
            )}
            )
            ),

//Remove unneccesary columns
//Expand and type the new table columns
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Year-Month", "missing", "All"}),
    #"Expanded Added Rows" = Table.ExpandTableColumn(#"Removed Columns", "Added Rows", {"Date", "Item", "built"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Added Rows",{{"Date", type date}, {"Item", type text}, {"built", Percentage.Type}})
in
    #"Changed Type"

Before

After