Mapping for different column with same value

[Anonymous]

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?

[SpiroswayGR]

@Anonymous 

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

[Anonymous]

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]

@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]

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

[v-diye-msft · Accepted solution]

Hi @Anonymous 

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

[Anonymous]

So is there any possiblility make a axis the same in one graph in live connection?