Forum Discussion

JorgeAbiad's avatar
JorgeAbiad
Icon for Helper III rankHelper III
6 years ago
Solved

Insert Date Field in the Market Data table

Hello PBI experts,

 

I have a requirement to create probably bar/line chart show the budget and forecast values for each year starting from 2020 up to 2039. But the budget and forecast for each year are included in the same table.

In the required bar/line chart, the X-axis should be representd by Year. But the problem is there is no date dimension in the table that relates directly to forecast or budget. My question is how do I insert a Year field? Please help as I've been struggling on this for 2 weeks. Please  see below for sample data.

Market TypeNameCustomer GroupBudget 2024Budget 2022Budget 2020Budget 2021Budget 2023CountryPlatformCurrent FC 2024Current FC 2023Current FC 2022Current FC 2021Current FC 2020BrandRegion
Commercial VehicleMD market dataCust Group Test              
Platform Light VehicleAnother Market DataCust Group Test              
Platform Commercial Vehiclemarket data 3Cust Group Test              
Platform Light VehicleMQB37Volkswagen              
Platform Light VehicleMQB38Volkswagen              
Platform Light VehicleMLB(w)Volkswagen              
Platform Light VehicleMLB49Volkswagen              
Light VehicleA3 (-3) SB/LIM (CN)Volkswagen100000100000100000100000100000ChinaMQB37100000100000100000100000100000AUDIAsia
Light VehicleA3 (-4)SB/ Lim (CN)Volkswagen100000100000100000100000100000ChinaMQB38100000100000100000100000100000AUDIAsia
Light VehicleA4L (B10) (CN)Volkswagen100000100000100000100000100000ChinaMLB(w)100000100000100000100000100000AUDIAsia
Light VehicleA4L (B9) (CN)Volkswagen100000100000100000100000100000ChinaMLB49100000100000100000100000100000AUDIAsia

 

Thanks

JorgeAbiad

8 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi JorgeAbiad ,

     

    We can use pivot to meet your requirement

     

    1. Select All the budget and current fc columns, then pivot them

     

    2. Replace the  “Current FC" with "Current_FC" to make split more easier

     

    3. Split Attribute column with space

     

     

     
    If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that you have shared.
     


    Best regards,

     

    • JorgeAbiad's avatar
      JorgeAbiad
      Icon for Helper III rankHelper III
      6 years ago

      Hello v-lid-msft ,

       

      Thank you for your response. I will try this approach if this will work according to the requirement.

      I'm quite new to Power BI so I am not yet fully aware of its awesome features.

      I would like to clarify the following questions:

      1. How do I choose all the Budget and Forecast columns? Sorry but I could not find the way to select multiple columns all at the same time.

      2. After pivoting the columns selected above, will they remain in the original table?

      3. What will happen to the new records added to the original table? Will they be transformed or pivoted automatically?

      Thank you very much again:)

       

      Regards

      JorgeAbiad

      • v-lid-msft's avatar
        v-lid-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi JorgeAbiad ,

         

        Sorry for our late reply, We can use ctrl to multi choose the column,  After pivoting the columns selected above, they will not remain in the original table, if you want to keep the origin table, we suggest you to duplicate one, If the new record is row, it will be pivote after each refresh, but the new column might need to change the query.

         


        Best regards,

         

  • JorgeAbiad's avatar
    JorgeAbiad
    Icon for Helper III rankHelper III
    6 years ago

    Hello PBI experts,

     

    If you think of any possible solution to this, please share it.

    Thanks

     

    Regards

    JorgeAbiad