Comparing Two Simple Tables

[ericOnline]

Since I'm new to Power BI, I'm not confident I'm doing things correctly. Can someone with experience look over theses steps and provide some insights?

Scenario:

- Two simple tables of weather data for the same time period

- One is forecasted values, one is actual values

- I want to compare forecasted vs. actual values such as "temp"

- Seems like a line chart would be best to show these differences

Data Cleanup:

- Imported JSON flat files

- Expanded all the columns

- Removed the Units columns and left only the Values

- Named the columns so I could tell them apart (FC = "forecasted", AC = "actual")

- I also did "Merge Queries As New" and merged both tables on the "time" column using all types of Left/Right/All Joins

- Unfortunately, there are still TWO TIME columns in the merged table (FC_time and AC_time)

Visualization:

- Started off with a Stacked Bar Chart to show differences (oops! Thanks @v-jiascu-msft and@Greg_Deckler)

- Moved to Line Chart instead

- Using FC_time for Axis <--Here is one area I'm confused.

- Using FC_temp and AC_temp as Values

Issues:

1. How do I merge the two tables on the date columns of each so I only have one date column?
2. How do I get the timeline organized by date? I can't tell the order currently, but its not sequential.

Thank you for the guidance early on! 

[Greg_Deckler]

One way to get a single column of date/time values for both tables would be to create a calculated table in DAX like this:

Time Table = DISTINCT(UNION(DISTINCT('Table1'[Time]),DISTINCT('Table2'[Time])))

You can then relate this to your other two tables and use it as your axis.

As for the sorting correctly, your column as currently displayed is probably coming through as text so it is sorting alphabetically. I'll see if I can take your time values and parse them out into something that would sort correctly.

[ericOnline]

Whew! Ok. Here we go!

Lets see if i'm following correctly:

• Click "Modeling" Tab
• New Table
• Put your DAX in there
• A DateTime table is created

• Click the Relationships tile
• Drag a line FROM each weather table TO the dateTime table

• Drag the mergedTimes column of the dateTime table to the Axis of the Line Chart
• Drag the temp columns of each weather table to the Values of the Line Chart

Now to get the date sort correct...

[ericOnline]

Hm. I'm not sure of the next piece of DAX you shared. I'll need to study it a bit. Your previous function was literally the first I've ever typed!

I went in and changed each time column to DATE-TIME data type. It appears Power BI changed the date time Text FROM UTC TO UTC-7 which is my local time (interesting).

QUESTION: What happens if the data is from Central Timezone? Is there a way to control the conversion? 

This visualization seems to now line up nicely, though I lost the hourly indicator. 

QUESTION: How do I show the hourly indicator on the X axis?

[Greg_Deckler]

Can probably control the conversion, would have to see about that, is that conversion happening in Power Query, looks like it.

To get your hours back, click on the visual, click on the Format (paint roller) icon. Expand x-axis and change from Continuous to Categorical.

[ericOnline]

Not sure how to tell if the conversion is happening in Power Query. I clicked on the column header and chose "Date/Time".

RE: Getting hours back...

• Clicking "Categorical" results in what appear to be time steps but the hours are still not visible in the label
• That is with "Concatenate labels" set to Off

I just prepared another data set, same table type, same data prep, EVERYTHING and I'm getting only SUMS of the columns. I can't get the VALUES. This is frustrating to not be able to see nor determine what the problem is! As far as I can tell, the four tables are identical in how they are set up!!

[ericOnline]

The Line Chart is picking up the values of MT_FC_temp as units in the thousands (4,800, 3,900, etc.) when they are clearly NOT that high.

What am I missing here?

[Greg_Deckler]

Any possibility you can share the PBIX via OneDrive or dropbox? These kinds of things are so difficult to troubleshoot sometimes.

[ericOnline]

Greg,

This link will be good till Friday. I appreciate all your expertise today. Thank you very much for taking a peek at this.

[ericOnline]

What do you say @Greg_Deckler? Were you able to see what I'm doing wrong with the second Line Chart?

[Greg_Deckler · Accepted solution]

@ericOnline Yep, sooooo much easier to see what is going on! Basically, you don't have any relationship for between your Time Table and your Glasgow tables so it is just grabbing all values because nothing filters for any time in your Time Table. You would want to create another Time Table for Glasgow would be my opinion and relate that to your Glasgow tables just like what you did with your Seattle tables. 

[ericOnline]

Hm. I'm going to have to wrap my head around the concept of adding date tables when comparing things. I saw Patrick from Guys in a Cube doing this recently. 

Thanks again for all of your expertise. Really above and beyond!

Take care

[Greg_Deckler]

See if something like this works for your time conversion, the new column should sort correctly:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNA1NNA1tAwxMrYyMIAgA4MopVgdhKyRQQhMygoiGQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom Column" = Table.AddColumn(Source, "Custom", each Text.Combine({Text.Middle([Column1], 5, 2), "/", Text.Middle([Column1], 8, 2), "/", Text.Combine(List.Transform(Splitter.SplitTextByDelimiter("T", QuoteStyle.None)([Column1]), each Text.Start(_, 4)), " "), "0:00"}), type text),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom Column",{{"Custom", type datetime}})
in
    #"Changed Type"