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 gr...
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.

     

     

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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi,

        I'm in live connection and can't edit quieries.

        Actually I also want to know whether it's possible only extracting the value of certain volumn in live environment...