How to split columns with different datatype for calculation and data model

[Anonymous]

I have a table as shown below:

I want to do some calculation on Stretch, OnTarget and Threshold so i can place them in a line chart. I have a date table which has a bridgetable that line FY on the table to FY in bridging Table to FY in DateTable.

However, this table has 3 different datatype which is difficult for me. how can i work with this issue.

Do i split them? I confused.

How can I achieve this so I can put Stretch, OnTarget and Threshold in a line chart

[Ashish_Mathur]

Hi,

Share data in a format the can be pasted in an MS Excel file.  From which month does the FY start? 

[Anonymous]

the file look like:

Company	FY	TargetID	TargetType	Title	Unit	Stretch	OnTarget	Threshold
Staple	2022-23	PK1	P Key	Alpha	%	0.8	0.85	0.9
Staple	2022-23	PK2	P Key	Beta	Date	23/05/2022	24/06/2022	25/07/2022
Staple	2022-23	PK3	P Key	Common	Number	150	155	160
Staple	2022-23	PK4	P Key	Delta	Text	This is Stretch	This is OnTarget	This is Threshold
Staple	2022-23	TR1	T Rail	AFade	Date	23/04/2022	24/05/2022	25/06/2022
Staple	2022-23	TR2	T Rail	SailFade	Number	300	400	500
Staple	2022-23	OP1	Other	ORail	Date	23/05/2022	24/06/2022	25/07/2022
Staple	2022-23	OP2	Other	Omake	%	0.6	0.7	0.8

The FY start from 2022-23

[Ashish_Mathur]

That data is all garbled.  Share it in a format the can be pasted in an MS Excel file.  Also, from which month does the FY start?

[Anonymous]

This is the File link below

Book1.xlsx

Note: Target_and_Period_Set Tab is the target planned for the whole year and will never change.
Actual by period is set on monthly or period basis. I have only period 1 and 2.
I am trying to clean the data for visualization because of the unit column and the P1 to P12 columns have different datatypes.

[Ashish_Mathur]

Hi,

I still do not know what you want.  I have done some work on tab1.  This M code does some transformations on the data in that tab.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZRNb9swDIb/imGgt7qQKMkfx23FdiiwFI1vRQ9uI8TBnLpINWD995NI13ld1IcCCanHpB3SfMP7+3wbupfB55c5KaKCTDzd3uhksxv/Fv234aXvor+IX3VVi3XsGralQClUGXGVJGrMryWlkZQmpegrpcU5cU3+cLlSFUFV331IRV13gbNMoVyRchPYQpUzuEJV76DMB6gRGgCtEDQC4T0cMRMQgkGwCA6hFFjt2UDPP8bjcXyOh99/j4/+lN6XU2z53ZV8JrF8xfDZ8Nny2brP7+IZ6orPlVsvx0I5137gGbT+X0iuP7xm8bMNJx+eeriyeW67095jUtuf/Gs/Drs0ym6Xnd12OOz7MLxl+3GE6K8FxekHfgHLez9xK520d0l1bXbXHYak8Z/dzi/lZFFODuU0aysO/BxJsIhUCDVCAwBCIxQaodAIhUYoNEKh0Sy01a4Ju95GNzU+K8qopAHL1rElsawrYhURa4ZqNecbjhqOGo6a+vwcG6MrFW1u0xw2oecf30yFffFfrac38g4GwSI4hBJh8bQaoQGAaWmclsZp6Xlaq00TNn3s/qS47NeS7bQ82crqdLIzncTdtHkpxwUs+ZWs3MrmuIfFxpIe/gM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Company = _t, FY = _t, TargetID = _t, TargetType = _t, Title = _t, Unit = _t, Stretch = _t, OnTarget = _t, Threshold = _t, P1 = _t, P2 = _t, P3 = _t, P4 = _t, P5 = _t, P6 = _t, P7 = _t, P8 = _t, P9 = _t, P10 = _t, P11 = _t, P12 = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index", "Unit", "Title", "TargetType", "TargetID", "FY", "Company"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Unit]), "Unit", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"%", Percentage.Type}, {"Date", type date}, {"Number", type number}})
in
    #"Changed Type"

Hope this helps.

[Padycosmos]

Hope this helps:

https://learn.microsoft.com/en-us/power-query/pivot-columns

[andhiii079845]

Than create 3x tabels: one with unit, one with % and one with Date. In this style the table is not usable. 

[andhiii079845]

If you do not need "%", "Date" and only need "Number" in the Strech, OnTarget and Threshold table, delete this rows in Power Query Editor and change than the data type. 

 

Filter rows:

Delete the Unit column, change data type:

[Anonymous]

@andhiii079845  thanks for your reply.

However, i need all the unit for the line chart or other chart and not only Number.