Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Power Query to repeat categories in rows for each date

Hi everyone,

 

I have a table of produced units by date, channel and type. I receive a daily excel file from a client, e.g:

 

plantas of date production date channel type 1type 2
michigan7/25/2021 7/20/2021 calls 12
ohio7/25/2021 7/20/2021 webpage 23
florida7/25/2021 7/20/2021 sales rep 23
Michigan7/25/2021 7/20/2021 webpage 12
Ohio7/25/2021 7/20/2021 sales rep 11
Florida7/25/2021 7/20/2021 calls 34
Michigan7/25/2021 7/20/2021 sales rep 12
Ohio7/25/2021 7/20/2021 calls 01
Florida7/25/2021 7/20/2021 webpage 21
Michigan7/25/2021 7/21/2021 calls 13
Ohio7/25/2021 7/22/2021 calls 23
Florida7/25/2021 7/22/2021 webpage 13

 

When there are no units producted type 1 and 2, the report does not specify that the values are 0, like in the example above. In the production date 7/21/2021, for example, Ohio and Florida didn't have any quantities from calls, sales reps and webpage.

 

How can I make a M query, so the channels "calls", "webpage", and "sales rep", are always shown for each one of the plants, and production dates, even if there are no quantities produced? The expected table would look like:

 

plantas of date production date channel type 1type 2
Michigan7/25/2021 7/20/2021 calls 12
Ohio7/25/2021 7/20/2021 webpage 23
Florida7/25/2021 7/20/2021 sales rep 23
Michigan7/25/2021 7/20/2021 webpage 12
Ohio7/25/2021 7/20/2021 sales rep 11
Florida7/25/2021 7/20/2021 calls 34
Michigan7/25/2021 7/20/2021 sales rep 12
Ohio7/25/2021 7/20/2021 calls 01
Florida7/25/2021 7/20/2021 webpage 21
Michigan7/25/2021 7/21/2021 calls 13
Ohio7/25/2021 7/21/2021 webpage 00
Florida7/25/2021 7/21/2021 sales rep 00
Michigan7/25/2021 7/21/2021 webpage 00
Ohio7/25/2021 7/21/2021 sales rep 00
Florida7/25/2021 7/21/2021 calls 00
Michigan7/25/2021 7/21/2021 sales rep 00
Ohio7/25/2021 7/21/2021 calls 00
Florida7/25/2021 7/21/2021 webpage 00
Michigan7/25/2021 7/22/2021 calls 00
Ohio7/25/2021 7/22/2021 webpage 00
Florida7/25/2021 7/22/2021 sales rep 00
Michigan7/25/2021 7/22/2021 webpage 00
Ohio7/25/2021 7/22/2021 sales rep 00
Florida7/25/2021 7/22/2021 calls 00
Michigan7/25/2021 7/22/2021 sales rep 00
Ohio7/25/2021 7/22/2021 calls 23
Florida7/25/2021 7/22/2021 webpage 13

 

Thank you

Need Help
  • BA_Pete's avatar
    BA_Pete
    5 years ago

    Anonymous ,

     

    Create a new blank query and paste this over the default code in Advanced Editor:

     

    let
  // Define Date.Today
  Date.Today = Date.From(DateTime.LocalNow()),
  Source = { Number.From(Date.StartOfYear(Date.AddYears(Date.Today, -2)))..Number.From(Date.Today) },
  convToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  chgDateType = Table.TransformColumnTypes(convToTable, {{"Column1", type date}}),
  renCol = Table.RenameColumns(chgDateType,{{"Column1", "asOfDate"}}),
    addProductionDate = Table.AddColumn(renCol, "productionDate", each List.Transform({ Number.From(Date.StartOfYear([asOfDate]))..Number.From(Date.EndOfYear(Date.AddYears([asOfDate], 1))) }, each Date.From(_))),
    expandProductionDate = Table.ExpandListColumn(addProductionDate, "productionDate"),
    addPlantCJ = Table.AddColumn(expandProductionDate, "plant", each plant),
    expandPlantCJ = Table.ExpandTableColumn(addPlantCJ, "plant", {"plant"}, {"plant"}),
    addChannelCJ = Table.AddColumn(expandPlantCJ, "channel", each channel),
    expandChannelCJ = Table.ExpandTableColumn(addChannelCJ, "channel", {"channel"}, {"channel"}),
    addUniqueRowID = Table.AddColumn(expandChannelCJ, "uniqueRowID", each Text.Combine({Text.From(Number.From([asOfDate]), "en-GB"), Text.From(Number.From([productionDate]), "en-GB"), [plant], [channel]}, "-"), type text),
    chgTypes = Table.TransformColumnTypes(addUniqueRowID,{{"productionDate", type date}, {"plant", type text}, {"channel", type text}})
in
  chgTypes

     

     

    You will need your plant unique list and channel unique list already set up in Power Query (and called exactly 'plant' & 'channel') for this to work.

     

    You can now follow the steps I took to generate this template table.

     

    Pete

6 Replies

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    5 years ago

    Hi Anonymous ,

     

    I would start by building the template table before adding in the values via merge in Power Query, or relationships in the data model.

    Start by identifying how you will dynamically create your [as of date] and [production date] columns. There's not enough information in your post for me to understand where these come from, but you should be able to use your calendar table or similar to create two columns that contain every combination of [as of date] and [production date] that you need in your table.

    Once you have those, then you will want to crossjoin your plants and channels into this table. You will need a plants table that contains a unique list of all the plants you want to see, and the same for channels.

    In your template table, add a new column, call it 'plants', and in the code window just type the name of the table that contains all the plant names. Expand this new column and you will see that it duplicates your date columns for each unique plant value.

    Do the same for channels.

    Now you have a template table that contains all possible combinations of these dimensions.

    From here you would create a unique Id field for each row in both your template table and your fact table, maybe by combining the values of each field into a single field, then either merge on your fact table to get the values where they exist and replace nulls with 0, or use your unique ID field in the data model to relate this template table to your fact table for the same effect.

     

    Pete

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Pete,

       

      Thank you for the idea you provided. I was hoping there was a query I could run to generate such table.

       

      The data comes from an ERP as a daily excel file, unfortunately there is no other way to get the data at this time. I save the daily files in a SharePoint folder, so Power BI can merge them.

      I will generate a template table first.

       

      Do you know how to generate a combination of "as of date" and "production date", starting with as of date (January 1, 2019), until today? For each "as of date" there are production dates of the as of date year, up to the next year. E.g. the "as of date" of January 1st, 2019 contains production dates from January 1st, 2019 up to December 31, 2020.

       

      "as of date" January 2, 2019 contains production dates from January 1,2019 up to December 31,2020.

       

      Please let me know, I have done research but nothing comes close.

       

      Thank you

      • BA_Pete's avatar
        BA_Pete
        Icon for Super User rankSuper User
        5 years ago

        Anonymous ,

         

        Create a new blank query and paste this over the default code in Advanced Editor:

         

        let
  // Define Date.Today
  Date.Today = Date.From(DateTime.LocalNow()),
  Source = { Number.From(Date.StartOfYear(Date.AddYears(Date.Today, -2)))..Number.From(Date.Today) },
  convToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  chgDateType = Table.TransformColumnTypes(convToTable, {{"Column1", type date}}),
  renCol = Table.RenameColumns(chgDateType,{{"Column1", "asOfDate"}}),
    addProductionDate = Table.AddColumn(renCol, "productionDate", each List.Transform({ Number.From(Date.StartOfYear([asOfDate]))..Number.From(Date.EndOfYear(Date.AddYears([asOfDate], 1))) }, each Date.From(_))),
    expandProductionDate = Table.ExpandListColumn(addProductionDate, "productionDate"),
    addPlantCJ = Table.AddColumn(expandProductionDate, "plant", each plant),
    expandPlantCJ = Table.ExpandTableColumn(addPlantCJ, "plant", {"plant"}, {"plant"}),
    addChannelCJ = Table.AddColumn(expandPlantCJ, "channel", each channel),
    expandChannelCJ = Table.ExpandTableColumn(addChannelCJ, "channel", {"channel"}, {"channel"}),
    addUniqueRowID = Table.AddColumn(expandChannelCJ, "uniqueRowID", each Text.Combine({Text.From(Number.From([asOfDate]), "en-GB"), Text.From(Number.From([productionDate]), "en-GB"), [plant], [channel]}, "-"), type text),
    chgTypes = Table.TransformColumnTypes(addUniqueRowID,{{"productionDate", type date}, {"plant", type text}, {"channel", type text}})
in
  chgTypes

         

         

        You will need your plant unique list and channel unique list already set up in Power Query (and called exactly 'plant' & 'channel') for this to work.

         

        You can now follow the steps I took to generate this template table.

         

        Pete