Need Help with Data Over Time using Line Graph

[Anonymous]

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!

[ChrisMendoza · Accepted solution]

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

[ChrisMendoza]

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

[Anonymous]

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.

[ChrisMendoza · Accepted solution]

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

[Anonymous]

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?

[ChrisMendoza · Accepted solution]

@Anonymous -

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

[Anonymous]

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

[Anonymous]

Got it. thanks for the help! @ChrisMendoza 

[Anonymous]

This worked perfectly! Thank you for the detailed explanation.