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

Question

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", ]} )&nbsp;My dataset has several kinds of itens and many rows. But a simple example is like this:DateItembuilt10/10/2021GSM100%11/10/2021LTE100%12/10/2021TDMA100%15/01/2022LTE100%17/01/2022TDMA100%&nbsp;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:&nbsp;01/01/2022GSM-3&nbsp;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.&nbsp;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&nbsp;&nbsp;

ronrsnfld · Accepted Answer

Create a list of all the ItemsGroup By "Year-Month"Examine each subtable to create a list of the missing ItemsUse List.Accumulate to create a table of the missing itemsCombine that table with the original subtable.&nbsp;&nbsp;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)=&gt; List.RemoveNulls(
        List.Generate(
            ()=&gt;[pos=List.PositionOf(t[Item],#"All Items"{0}), idx=0],
            each [idx] &lt; 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)=&gt; state &amp; 
                    {[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"&nbsp;BeforeAfter&nbsp;&nbsp;

Forum Discussion

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

1 Reply

Related content

Circular dependency

Best Practice Analyzer

Dax query dependency

dependent slicers

Dependent Filters and Filter Depend on Slicer

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%