topic Re: Line chart points from columns in Developer

Line chart points from columns

crizbe — Fri, 03 Jun 2022 13:51:16 GMT

I have the following SharePoint lists:

Plans:

Plan	Curve	Description
Plan001	Curve001
Plan002	Curve002

Curves:

Curve	ValueA	MeasureA	ValueB	MeasureB
Curve001	10	100	50	100
Curve002	20	30	40	80

A User can select a Plan. When it gets selected, I want to draw a lina chart of the corresponding curve, where the datapoints consist of Value (X-Axis) and Measure (Y-Axis).

I managed to get all the values with either measure column or calculation column. But I am not able to draw the chart. I think it is because each field in curve is also a column.

The chart works perfectly when I create a table like this from hand:

Value	Measure
10	100
50	100

Is there any way to create such a table automatically when a Plan gets selected? Or do you know any other way how I could achieve this?

Re: Line chart points from columns

lbendlin — Sun, 05 Jun 2022 01:02:48 GMT

Yes, usually you would need to unpivot your Curves table - in this case in pairs.

What's the importance of "A" and "B" - does that play any role later?

Re: Line chart points from columns

crizbe — Sun, 05 Jun 2022 14:13:21 GMT

Thanks, yes A and B are the X and Y Values of each point in the chart

Re: Line chart points from columns

lbendlin — Mon, 06 Jun 2022 01:13:01 GMT

Here is one possible approach

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tKks1MDBU0lEyNAATINIUxozVgSsxAgoZgcSNQYQJiLAAKogFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Curve = _t, ValueA = _t, MeasureA = _t, ValueB = _t, MeasureB = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Curve", type text}, {"ValueA", Int64.Type}, {"MeasureA", Int64.Type}, {"ValueB", Int64.Type}, {"MeasureB", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "List", each {{[ValueA],[MeasureA]},{[ValueB],[MeasureB]}}), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Curve", "List"}), #"Expanded List" = Table.ExpandListColumn(#"Removed Other Columns", "List"), #"Extracted Values" = Table.TransformColumns(#"Expanded List", {"List", each Text.Combine(List.Transform(_, Text.From), "#(tab)"), type text}), #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "List", Splitter.SplitTextByDelimiter("#(tab)", QuoteStyle.Csv), {"x", "y"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"x", Int64.Type}, {"y", Int64.Type}}) in #"Changed Type1"

Re: Line chart points from columns

crizbe — Tue, 07 Jun 2022 08:43:01 GMT

Thank you very much. It worked like a charm! I have one slight issue now. How am I able to filter the values now based on selectedValues?

Re: Line chart points from columns

Anonymous — Tue, 07 Jun 2022 08:45:13 GMT

Hi @crizbe ,

I agree with lbendlin's reply. Due to we cannot see calculated column or measure created by Dax in Power Query Editor, firstly, you need to calcualte MeasureA and MeasureB by M code in Power Query Editor. Then you can follow lbendlin's step to unpivot your table.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Line chart points from columns

Anonymous — Tue, 07 Jun 2022 08:52:51 GMT

Hi @crizbe ,

I think you can create a relationship between Plan table and Curve table by [Curve] column.

For reference: Create and manage relationships in Power BI Desktop

Then you can create a slicer by [Plan] column from Plan table.

By default:

Select Plan001:

Select Plan002:

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Line chart points from columns

crizbe — Tue, 07 Jun 2022 09:01:54 GMT

Thank you so much! It works as expected now.