Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Mapping for different column with same value

Dear all,

I'm now working on power bi connecting a live database. In the database, there are two time buckets( which have common value), relete to two sets of data that I'd like to compare in one graph. As the x axis are different, I can't compare directly.  Can you help solve it?

 

 

 

Need Help
  • v-diye-msft's avatar
    v-diye-msft
    6 years ago

    Hi Anonymous 

     

    There's no ways to add the calendar table under the live-connection mode unless you add it in the data source.

     

     

6 Replies

  • SpiroswayGR's avatar
    SpiroswayGR
    Resolver III
    6 years ago

    Anonymous 

    If you create a calendar table and relate both "Tables" of your database then i think you can graph them easily.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      can you explain a bit more how to create a calendar and then relate to tables? my value in column is like "2018jun", do I need to convert it to date format or? cuz when I set a new measure

      TestDate = CALENDAR(DATE(2019;1;1);DATE(2019;12;12)), there is a error message "a table of multiple values was supplied where a single value was expected".
      Thanks in advance
      • SpiroswayGR's avatar
        SpiroswayGR
        Resolver III
        6 years ago

        Anonymous Please use PowerM script

        1st ) Go Edit queries

        2nd ) Go Advanced editor 

        and then script 

         

        let
        StartDate = #date(2017,1,1),
        EndDate = #date(2021,12,31),
        NumberOfDays = Duration.Days( EndDate - StartDate ),
        Dates = List.Dates(StartDate, NumberOfDays+1, #duration(1,0,0,0)),
        #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "FullDateAlternateKey"}}),
        #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"FullDateAlternateKey", type date}}),
        #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([FullDateAlternateKey]), type number),
        #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([FullDateAlternateKey]), type number),
        #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([FullDateAlternateKey]), type text),
        #"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([FullDateAlternateKey]), type number),
        #"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter", "Week of Year", each Date.WeekOfYear([FullDateAlternateKey]), type number),
        #"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([FullDateAlternateKey]), type number),
        #"Inserted Day" = Table.AddColumn(#"Inserted Week of Month", "Day", each Date.Day([FullDateAlternateKey]), type number),
        #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day", "Day of Week", each Date.DayOfWeek([FullDateAlternateKey]), type number),
        #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([FullDateAlternateKey]), type number),
        #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Year", "Day Name", each Date.DayOfWeekName([FullDateAlternateKey]), type text)
        in
        #"Inserted Day Name"

         

        Don't forget to give thumbs up 👍and accept this as a solution if it helped you.