topic Re: Need Help with Data Over Time using Line Graph in Desktop

Need Help with Data Over Time using Line Graph

Anonymous — Tue, 21 Jan 2020 17:54:05 GMT

Hello There,

I'm new to Power BI and I just want to graph basic sales by period for each account.

I imported an excel table with column A being the accounts, and columns B-N being periods 1-13 and the sales $ for each account by period.

However I want to use the Periods as both the Value and the X axis, however it is forcing me to use the accounts as the x axis.

Is there an easy way to get around this without reformatting in excel?

I greatly appreciate the help!

Re: Need Help with Data Over Time using Line Graph

ChrisMendoza — Tue, 21 Jan 2020 18:06:21 GMT

Anonymous -

You would do your data manipulation of the cross tabulated (pivoted) data in the Power Query Editor.

Something like:

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY9BDgAhCAP/4nkPggLyFuNB/f8flrLJXhpSaCfMWfbe5SnKGjq8hlpvoSwE36DMHb4bHMG2i4dShwrBaYoGJirrmeWcg4SgzzUTFR3a0GctCSNprtnB4KslX/97tySEg9Z7L/aKDs+cViT6dzW+9MCsAkJLQn5CVv9/KP0WP6z1Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [accounts = _t, #"1" = _t, #"2" = _t, #"3" = _t, #"4" = _t, #"5" = _t, #"6" = _t, #"7" = _t, #"8" = _t, #"9" = _t, #"10" = _t, #"11" = _t, #"12" = _t, #"13" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"accounts", type text}, {"1", Int64.Type}, {"2", Int64.Type}, {"3", Int64.Type}, {"4", Int64.Type}, {"5", Int64.Type}, {"6", Int64.Type}, {"7", Int64.Type}, {"8", Int64.Type}, {"9", Int64.Type}, {"10", Int64.Type}, {"11", Int64.Type}, {"12", Int64.Type}, {"13", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"accounts"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Period"}, {"Value", "Sales"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Period", Int64.Type}}) in #"Changed Type1"

Re: Need Help with Data Over Time using Line Graph

Anonymous — Tue, 11 Feb 2020 21:55:11 GMT

Thanks for the quick response! So the data currently is just in an excel table which I am uploading to Power BI.

This is the result I am getting.

Could you elaborate a little more on your solution please? I apologize, I'm new to this and very green.

Re: Need Help with Data Over Time using Line Graph

ChrisMendoza — Tue, 21 Jan 2020 18:43:22 GMT

Anonymous -

No worries. This table is not setup in a way that is helpful. I propose you should:

'Edit Queries' to get to the Power Query Editor.
- You will see exactly the same table but in the Editor
Right-Click column [ACCOUNT]
- Select and Click 'Unpivot Other Columns
- You should now see 3 columns (e.g. [ACCOUNT], [Attribute], [Value]
[Attribute] = your period names
[Value] = your sales amounts
You will likely need to change Data Types; for your [Value] to Whole Number, Decimal, etc.; You could also change the column names at this point to something more meaningful.

Anonymous wrote:

You'll likely encounter a sorting issue on your periods where {p1, p10, p11, p12, p13, p2, ..., p9}. It would be better if you could sort the column by a numeric value; when you get there we can assist at that point.

Your Visualizations tab should show something like:

Re: Need Help with Data Over Time using Line Graph

Anonymous — Tue, 21 Jan 2020 20:34:35 GMT

This worked perfectly! Thank you for the detailed explanation.

Re: Need Help with Data Over Time using Line Graph

Anonymous — Tue, 11 Feb 2020 19:38:16 GMT

Hey ChrisMendoza.

I now have included this data and I now want to make sure the x axis values are in the order I want. so P1-P13 in order.

I created another table and did a one to many link from my sort table, which has P1-1, P2-2 etc.

This is linked to my Table with the values.

I then tried to use theperiod from my new table as the x axis, but it still isn't in correct order. Could you please advise on what I am missing?

Re: Need Help with Data Over Time using Line Graph

ChrisMendoza — Tue, 11 Feb 2020 20:08:49 GMT

Anonymous -

Check what the Sort is on the actual visual. Seems like the visual is set on GSV $ descending.

Re: Need Help with Data Over Time using Line Graph

Anonymous — Tue, 11 Feb 2020 21:54:36 GMT

Hey ChrisMendoza

Thanks for the follow up!

Below are the screenshots. I believe I followed the process exactly as I've seen it on here but I must be m

Re: Need Help with Data Over Time using Line Graph

Anonymous — Tue, 11 Feb 2020 21:54:15 GMT

Got it. thanks for the help! ChrisMendoza