Forum Discussion

BoyerAlexis's avatar
BoyerAlexis
Frequent Visitor
4 years ago

Retrieve data in .each() and create columns

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.

1 Reply

  • AlB's avatar
    AlB
    Community Champion

    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.