PowerBI Line Graph where each value on the X Axis is from a different column

[Rambler1974]

Hello,

I have been struggling with this for some time so hope someone out there can help!

I've imported data into PowerBI desktop from an Excel table. I now want to create a PowerBI Line Graph where each value along the x axis is taken from a different column. 

The table is shown below. I want the user to be able to select the Project Title from a slicer / drop-down menu, then for the line graph to show the "Kg" for each month along the x-axis for that particular project. Then another line (if possible) for the "£" for each month along the x-axis for that particular project. Is this possible please?

Thanks in advance for any help with this. It's the first time I've had to post a question!

Project Title       Jan kg    Jan £         Feb kg     Feb £         Mar kg      Mar £ 

Project 1           222         £3,863       333         £5,794        444           £7,726  

Project 2           63          £1,096        12           £209           222           £3,863  

Project 3           55          £957           0             £0               63             £1,096  

Project 4           576        £10,022      78            £1,357        55             £957     

Project 5           984        £17,122      987          £17,174      576           £10,022             

[rajendraongole1 · Accepted solution]

Hi @Rambler1974 - Yes , it is possible go to the power query editor select the project title column>> use Unpivoted Other Columns select your table and unpivot the data so that each month and its corresponding values are in rows rather than columns.

split the column using delimiter space

final result at power query editor after all changes as below

advanced query editor : M formule

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY89DsMwCEavYnlmAPyDuUX3yFPVJUukqjfKTXKy2rh10i7oA/Ee9rL423NbH/eXIw+emVt1xx6g5NBiCL0eewLR2FKM0XoB4ewrnHgH81gmQM0tEVvLqFP9NV/JDqU07mqSFtA28d94hfozkuRBEQKaXspnPZjHrCZ1P3Afaxk/IQEyVovMgZz6aa/1DQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Title" = _t, #"Jan kg" = _t, #"Jan £ " = _t, #"Feb kg" = _t, #"Feb £" = _t, #"Mar kg" = _t, #"Mar £" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Title", type text}, {"Jan kg", Int64.Type}, {"Jan £ ", type text}, {"Feb kg", Int64.Type}, {"Feb £", type text}, {"Mar kg", Int64.Type}, {"Mar £", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Jan £ ", Text.Trim, type text}, {"Feb £", Text.Trim, type text}, {"Mar £", Text.Trim, type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Trimmed Text", {"Project Title"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2", "Attribute.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}, {"Attribute.3", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute.3"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute.1", "Month"}, {"Attribute.2", "Unit"}}),
#"Added Custom Column" = Table.AddColumn(#"Renamed Columns", "Custom", each let splitValue = List.Reverse(Splitter.SplitTextByDelimiter("£", QuoteStyle.None)(Text.From([Value], "en-US"))), splitsplitValue0 = Splitter.SplitTextByDelimiter(",", QuoteStyle.None)(splitValue{0}?) in Text.Combine(splitsplitValue0), type text),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom Column",{{"Custom", Int64.Type}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"Value"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "Value"}})
in
#"Renamed Columns1"

using line chart as below output

Hope it helps

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Anonymous]

Hi @Rambler1974 ,

Based on the description, @rajendraongole1 solution provided should help.

If you have solved the problem, please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Wisdom Wu

[Arul · Accepted solution]

@Rambler1974 ,

It is possible. Please refer the attached file. I did unpivot and split of some columns.

[Rambler1974]

Many thanks for your help, Arul. I shall look into this!

[rajendraongole1 · Accepted solution]

Hi @Rambler1974 - Yes , it is possible go to the power query editor select the project title column>> use Unpivoted Other Columns select your table and unpivot the data so that each month and its corresponding values are in rows rather than columns.

split the column using delimiter space

final result at power query editor after all changes as below

advanced query editor : M formule

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY89DsMwCEavYnlmAPyDuUX3yFPVJUukqjfKTXKy2rh10i7oA/Ee9rL423NbH/eXIw+emVt1xx6g5NBiCL0eewLR2FKM0XoB4ewrnHgH81gmQM0tEVvLqFP9NV/JDqU07mqSFtA28d94hfozkuRBEQKaXspnPZjHrCZ1P3Afaxk/IQEyVovMgZz6aa/1DQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Title" = _t, #"Jan kg" = _t, #"Jan £ " = _t, #"Feb kg" = _t, #"Feb £" = _t, #"Mar kg" = _t, #"Mar £" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Title", type text}, {"Jan kg", Int64.Type}, {"Jan £ ", type text}, {"Feb kg", Int64.Type}, {"Feb £", type text}, {"Mar kg", Int64.Type}, {"Mar £", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Jan £ ", Text.Trim, type text}, {"Feb £", Text.Trim, type text}, {"Mar £", Text.Trim, type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Trimmed Text", {"Project Title"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2", "Attribute.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}, {"Attribute.3", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute.3"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute.1", "Month"}, {"Attribute.2", "Unit"}}),
#"Added Custom Column" = Table.AddColumn(#"Renamed Columns", "Custom", each let splitValue = List.Reverse(Splitter.SplitTextByDelimiter("£", QuoteStyle.None)(Text.From([Value], "en-US"))), splitsplitValue0 = Splitter.SplitTextByDelimiter(",", QuoteStyle.None)(splitValue{0}?) in Text.Combine(splitsplitValue0), type text),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom Column",{{"Custom", Int64.Type}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"Value"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "Value"}})
in
#"Renamed Columns1"

using line chart as below output

Hope it helps

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Rambler1974]

Many thanks for your help, rajendraongole1. Very much appreciated!