Retrieve data in .each() and create columns

[BoyerAlexis]

Hi,

I'm kind of a begginer in PowerBI and I'm facing an issue :

I've got an existing table with datas in it, being updated every month by someone else.

In this table I've got colums named MonthCode, OperationalBase, Action like so :

5, Dijon, 6

5, Nevers, 2

5, Beaune, 9

6, Dijon, 5

6, Nevers, 8

In this example, I've got no data for Beaune in the month of June and I'd like it "write itself".

By that, I means that whenever a new month is detected (7 for this example), every city will be here no matter what, even if no data was inserted.

I'm trying to create something in the Power Query editor but I've got no bases.

Here's what I got right now :

 Test = List.Generate(
    ()=>0,
    each _<12, each _+1,
// check if _ exists in DataTable
    each Table.Column(
            [monthCode = _+1, city = "Dijon", action = DataTable['action']['Dijon']]
            [monthCode = _+1, city = "Nevers", action = DataTable['action']['Nevers']]
            [monthCode = _+1, city = "Beaune", action = DataTable['action']['Beaune']]
// maybe automatically create the rows without adding them manually to the code ??
        )
    ),

I'm not even sure if what i'm asking is possible, but I'd be grateful if you could help me.

[AlB]

Hi @BoyerAlexis 

Can you provide the expected output for the table you have shown, with an accompanying explanation. I do not quite understand what you need. Also, you talk about a new month being detected "7 for this example" but I see no 7 on the input data

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.