Matrix Excel data import to power bi in tablular format

[Mohammada]

Hi,

I am new to BI anyone guidelines would be really helpful. I have a daily report in matrix format of Excel which we would like to visualize in power bi. the user adds the data in excel for before day stock values and we want to present it to managers every day with visuals. As it is in a matrix format, if I import the excel in BI it will not export the right columns. Is there a way to automate the matrix data to tabular form and export it to Power bi? 

	PLANNED							ACTUALS
		05/10/2020	06/10/2020	07/10/2020	08/10/2020				05/10/2020	06/10/2020	07/10/2020	08/10/2020	09/10/2020
		Mon	Tue	Wed	Thu	Totals			Mon	Tue	Wed	Thu	Fri
	Boiler demand (tonnes)							Bunker stock at start of day (visual check)
	Boiler 1	325	325	325	325			Bunker 1	0	0	0	0	0
	Boiler 2	325	325	325	325			Bunker 2	0	0	0	0	0
	Boiler 3	325	325	325	325			Bunker 3	0	0	0	0	0
	Boiler 4	325	325	325	325			Bunker 4	0	0	0	0	0
	Boiler 5	325	325	325	325			Bunker 5	0	0	0	0	0
	Total	1625	1625	1625	1625			Total bunker stocks	0	0	0	0	0
	Throughput lost to unplanned	42	42	42	42
								Max. bunker capacity	10,000	10,000	10,000	10,000	10,000
	Planned burn	1,650	1,650	1,650	1,650	11,550
								Actual burn figure	0	0	0	0	0
	Demand met from							Demand met from
	Stock (opening balance)	2,900	3,611	4,272	4,883			Stock (opening balance)	2,900	0	0	0	0
	Inputs							Inputs
	BRC/FPP	550	550	550	550	2,750		BRC/FPP	0	0	0	0	0
	Hornsey Street.	470	470	470	470	2,350		Hornsey Street.	0	0	0	0	0
	Hendon.	140	140	140	140	700		Hendon.	0	0	0	0	0
	NLWA direct tipping	750	700	650	750	3,650		NLWA direct tipping	0	0	0	0	0
	HCC Waterdale / HCC Buntingford	250	250	250	250	1,500		HCC Waterdale.	0	0	0	0	0
	HCC direct tipping	85	85	85	85	425		HCC direct tipping	0	0	0	0	0
	ELWA	50	50	50	50	250		Shanks SRFF	0	0	0	0	0
	Other third party (S)	60	60	60	60	300		Countrystyle	0	0	0	0	0
	18-01-04.	2	2	2	2	9		Clinical.	0	0	0	0	0
	Others.	4	4	4	4	20		Others.	0	0	0	0	0
	Planned Inputs	2,361	2,311	2,261	2,361	11,954		Actual Inputs	0	0	0	0	0
	Stock (daytime peak)	5,261	5,922	6,533	7,243
	Stock (closing balance)	3,611	4,272	4,883	5,593			Stock (closing balance)	2,900	0	0	0	0
	Stock movement +/-	711	661	611	711	404		Stock movement +/-	0	0	0	0	0

And I want this to be changed into the below format whenever they make a change in the data i.e., every day

Actual	02/01/2020	1	1100	0	1600	1600	1800	0	10000	0	1681	6201	460	602	290	833	0	92	0	0	2	1	2280	0	6800	599
Plan	03/01/2020	1	330	330	330	330	330	42	0	1650	0	6822	0	0	0	0	0	0	0	0	0	0	0	6822	5172	-1650
Actual	03/01/2020	1	1600	700	1200	1500	1700	0	10000	0	1707	6800	0	0	0	0	0	0	0	0	0	0	0	0	5093	-1707
Plan	04/01/2020	1	330	330	330	330	330	42	0	1650	0	5172	500	600	300	800	0	80	0	0	1.5	4	2285.5	7457.5	5807.5	635.5
Actual	04/01/2020	1	1500	700	0	1400	1600	0	10000	0	1663	5093	446	465	271	857	0	105	0	0	2	1	2147	0	5577	484
Plan	05/01/2020	1	330	330	330	330	330	42	0	1650	0	5807.5	500	600	200	800	0	80	0	0	1.5	4	2185.5	7993	6343	535.5
Actual	05/01/2020	1	1700	1400	0	1600	1000	0	10000	0	1632	5577	916	417	147	869	0	93	0	0	2	1	2445	0	6390	813
Plan	06/01/2020	2	330	330	330	330	330	42	0	1650	0	6343	500	0	0	600	0	80	0	0	2	4	1186	7529	5879	-464
Actual	06/01/2020	2	1500	1600	1000	1200	1200	0	10000	0	1654	6390	515	0	0	750	0	66	0	0	0	0	1331	0	6067	-323

Regards,

Mohammad

[Jimmy801 · Accepted solution]

Hello @Mohammada 

I suppose you are reading from a pivottable. This is always tricky and needs some specific techniques to find a result. In my proposed solution i first identify the planned columns and the actual columns. Then the tables are separated and applied some steps liks removing tops rows, promote headers and and unpivot inclusive filter. Then both are again combines and pivoted

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pVZfb9owEP8qFk+d5lLHiQM8Ulq0SV2HSqc+VH1wE9NEBDtynGp8m32WfbLZToCM2INtknPOkbvf/fHdmefnweJuen9/ezOAg/6azh6/Te+Wxx9e4LPZERkGaIgRRoaJu8yoy4w7zH79izKa7JnWhS+Ca/pYM02fWGres9pQoWhR7Y35xOYyt0jXIi+YBCnbUJ6CCyU4Z9UHZ0qua77WopUSyRpQpV+oVECsQEq34OI9r2pagCRjybqn37EU/PyhfwkxcdLfLAUm8N7TwcLnIuFTSOG5SOEppOj8+KJTWG4MBxLxItly0FwQWy3n1iwrCV47h1z5UTMp6resrBUoRKWAEqDmZUF18ZgSi3CfuFdbzP31hX4f7pxJaEmTXG2Nx8hJDMyiMa+VpCn5AMbEuwWQkL90aJqo2uZHcrDK32rJvNnxRnts6Kbpug1TYCXFxil4QsbALG1HXoiS8Zy/gVeqM5Ew04QYTkzBwDgwvRRBPLLnAcfjcA9xSrsf32euD75yeuv+ZEv6YXY1Xyw0SwhyUgxH7aEcZN0J/iQkr9gWLJVkTA1NSCPkpBiGLWZfx4PNeCq4+R5EyElHqB3nB1E31P3d0xSkuWSJ7pC8LHV6jTo5gMTNu6VhU5t6ufU87s5m4IkqJlNaMHAFDK+HgtI6KyFNM+ImuT2qe6ANowvxh8RosZ5TY9InEe4AnxnGrQ7ZFALqk8ZbU6gZ5esKLB/mcy/OV5XpgaGyXKag1NeTvpiWppRj1Cfh7hxnQidMbiu1LfxNHYwvUXCJIpMgfPRMWpwi53lCC38OrXuVLdijZ/cX4SDhRthNuX2j6QqPg2YPmh3veLvrWTch0aA7w/a6/zm+2smhr3+VbxgoGbU3P2k9IHCCTXZiSEIzbkYQR+E5gIm+U45GkW+EEUgmvVHm0PeNskZjI97ZhnEFPl5dGketqdgG0ZhtfolQ1LVzpOVI5ssv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}}),
    
    IdentifyColumnWithCellActual = List.Select
    (
        List.Transform
        (
            Table.ColumnNames(#"Changed Type"),
            (trans)=> if List.Count(List.Select(Table.Column(#"Changed Type", trans),each _ = "ACTUALS" ))>0 then trans else null
        ), 
        each _ <> null
    ){0},
    IdentifyPlannedColumns = List.FirstN
    (
        Table.ColumnNames(#"Changed Type"),
        each _ <> IdentifyColumnWithCellActual
    ),
    IdentifyActualColumns = List.Difference
    (
        Table.ColumnNames(#"Changed Type"),
        IdentifyPlannedColumns
    ),
    CreatePlannedTable = 
    let 
        SelColumn = Table.SelectColumns(#"Changed Type", IdentifyPlannedColumns),
        RemoveTop1 = Table.Skip(SelColumn,1),
        PromoteHeaders = Table.PromoteHeaders(RemoveTop1, [PromoteAllScalars=true]),
        RemoveTop2 = Table.Skip(PromoteHeaders,1),
        UnpivotOtherThenFirst = Table.UnpivotOtherColumns(RemoveTop2, {""}, "Attribute", "Value"),
        TableSelectRows = Table.SelectRows
        (
            UnpivotOtherThenFirst,
            each Value.Is(try Date.From(_[Attribute]) otherwise "", type date)  and _[Value]<>""
        ),
        AddPlan = Table.AddColumn(TableSelectRows, "Type", each "Planned")
    in 
        AddPlan,
    
    CreateActualTable = 
    let 
        SelColumn = Table.SelectColumns(#"Changed Type", IdentifyActualColumns),
        RemoveTop1 = Table.Skip(SelColumn,1),
        PromoteHeaders = Table.PromoteHeaders(RemoveTop1, [PromoteAllScalars=true]),
        RemoveTop2 = Table.Skip(PromoteHeaders,1),
        UnpivotOtherThenFirst = Table.UnpivotOtherColumns(RemoveTop2, {""}, "Attribute", "Value"),
        TableSelectRows = Table.SelectRows
        (
            UnpivotOtherThenFirst,
            each Value.Is(try Date.From(_[Attribute]) otherwise "", type date)  and _[Value]<>""
        ),
        AddActual = Table.AddColumn(TableSelectRows, "Type", each "Actual")
    in 
        AddActual,
    
    CombineBoth =Table.Combine({CreatePlannedTable, CreateActualTable}),
    #"Changed Type1" = Table.TransformColumnTypes(CombineBoth,{{"Value", type number}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[#""]), "", "Value", List.Sum)
in
    #"Pivoted Column"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Jimmy801 · Accepted solution]

Hello @Mohammada 

I suppose you are reading from a pivottable. This is always tricky and needs some specific techniques to find a result. In my proposed solution i first identify the planned columns and the actual columns. Then the tables are separated and applied some steps liks removing tops rows, promote headers and and unpivot inclusive filter. Then both are again combines and pivoted

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pVZfb9owEP8qFk+d5lLHiQM8Ulq0SV2HSqc+VH1wE9NEBDtynGp8m32WfbLZToCM2INtknPOkbvf/fHdmefnweJuen9/ezOAg/6azh6/Te+Wxx9e4LPZERkGaIgRRoaJu8yoy4w7zH79izKa7JnWhS+Ca/pYM02fWGres9pQoWhR7Y35xOYyt0jXIi+YBCnbUJ6CCyU4Z9UHZ0qua77WopUSyRpQpV+oVECsQEq34OI9r2pagCRjybqn37EU/PyhfwkxcdLfLAUm8N7TwcLnIuFTSOG5SOEppOj8+KJTWG4MBxLxItly0FwQWy3n1iwrCV47h1z5UTMp6resrBUoRKWAEqDmZUF18ZgSi3CfuFdbzP31hX4f7pxJaEmTXG2Nx8hJDMyiMa+VpCn5AMbEuwWQkL90aJqo2uZHcrDK32rJvNnxRnts6Kbpug1TYCXFxil4QsbALG1HXoiS8Zy/gVeqM5Ew04QYTkzBwDgwvRRBPLLnAcfjcA9xSrsf32euD75yeuv+ZEv6YXY1Xyw0SwhyUgxH7aEcZN0J/iQkr9gWLJVkTA1NSCPkpBiGLWZfx4PNeCq4+R5EyElHqB3nB1E31P3d0xSkuWSJ7pC8LHV6jTo5gMTNu6VhU5t6ufU87s5m4IkqJlNaMHAFDK+HgtI6KyFNM+ImuT2qe6ANowvxh8RosZ5TY9InEe4AnxnGrQ7ZFALqk8ZbU6gZ5esKLB/mcy/OV5XpgaGyXKag1NeTvpiWppRj1Cfh7hxnQidMbiu1LfxNHYwvUXCJIpMgfPRMWpwi53lCC38OrXuVLdijZ/cX4SDhRthNuX2j6QqPg2YPmh3veLvrWTch0aA7w/a6/zm+2smhr3+VbxgoGbU3P2k9IHCCTXZiSEIzbkYQR+E5gIm+U45GkW+EEUgmvVHm0PeNskZjI97ZhnEFPl5dGketqdgG0ZhtfolQ1LVzpOVI5ssv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}}),
    
    IdentifyColumnWithCellActual = List.Select
    (
        List.Transform
        (
            Table.ColumnNames(#"Changed Type"),
            (trans)=> if List.Count(List.Select(Table.Column(#"Changed Type", trans),each _ = "ACTUALS" ))>0 then trans else null
        ), 
        each _ <> null
    ){0},
    IdentifyPlannedColumns = List.FirstN
    (
        Table.ColumnNames(#"Changed Type"),
        each _ <> IdentifyColumnWithCellActual
    ),
    IdentifyActualColumns = List.Difference
    (
        Table.ColumnNames(#"Changed Type"),
        IdentifyPlannedColumns
    ),
    CreatePlannedTable = 
    let 
        SelColumn = Table.SelectColumns(#"Changed Type", IdentifyPlannedColumns),
        RemoveTop1 = Table.Skip(SelColumn,1),
        PromoteHeaders = Table.PromoteHeaders(RemoveTop1, [PromoteAllScalars=true]),
        RemoveTop2 = Table.Skip(PromoteHeaders,1),
        UnpivotOtherThenFirst = Table.UnpivotOtherColumns(RemoveTop2, {""}, "Attribute", "Value"),
        TableSelectRows = Table.SelectRows
        (
            UnpivotOtherThenFirst,
            each Value.Is(try Date.From(_[Attribute]) otherwise "", type date)  and _[Value]<>""
        ),
        AddPlan = Table.AddColumn(TableSelectRows, "Type", each "Planned")
    in 
        AddPlan,
    
    CreateActualTable = 
    let 
        SelColumn = Table.SelectColumns(#"Changed Type", IdentifyActualColumns),
        RemoveTop1 = Table.Skip(SelColumn,1),
        PromoteHeaders = Table.PromoteHeaders(RemoveTop1, [PromoteAllScalars=true]),
        RemoveTop2 = Table.Skip(PromoteHeaders,1),
        UnpivotOtherThenFirst = Table.UnpivotOtherColumns(RemoveTop2, {""}, "Attribute", "Value"),
        TableSelectRows = Table.SelectRows
        (
            UnpivotOtherThenFirst,
            each Value.Is(try Date.From(_[Attribute]) otherwise "", type date)  and _[Value]<>""
        ),
        AddActual = Table.AddColumn(TableSelectRows, "Type", each "Actual")
    in 
        AddActual,
    
    CombineBoth =Table.Combine({CreatePlannedTable, CreateActualTable}),
    #"Changed Type1" = Table.TransformColumnTypes(CombineBoth,{{"Value", type number}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[#""]), "", "Value", List.Sum)
in
    #"Pivoted Column"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Mohammada]

Hi Jimmy,

Thank you so much for your prompt response,much appreciated.

The code worked however I don't have the static data. the data comes in an email every day in the excel format. Is there a way I can automate to look at "X" file and it updates the data in BI.

[Jimmy801 · Accepted solution]

Hello @Mohammada 

if the code worked properly I would ask you to mark it as solution. About your request of Mail, you could use Exchange.Contents to read your Mailbox and in case apply filters according to your logic (sender, time, subject) and then read the according appendix

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Mohammada]

Hi Jimmy,

Thanks, I have accepted it as a solution and tried to use exchange and attachments. However, I receive the following error with the same code I have used.

Expression.Error: We cannot convert a value of type Table to type List.
Details:
Value=[Table]
Type=[Type].

Please see the below code.

let
Source = Table.FromRows(#"Mail (2)",let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}}),

IdentifyColumnWithCellActual = List.Select
(
List.Transform
(
Table.ColumnNames(#"Changed Type"),
(trans)=> if List.Count(List.Select(Table.Column(#"Changed Type", trans),each _ = "ACTUALS" ))>0 then trans else null
),
each _ <> null
){0},
IdentifyPlannedColumns = List.FirstN
(
Table.ColumnNames(#"Changed Type"),
each _ <> IdentifyColumnWithCellActual
),
IdentifyActualColumns = List.Difference
(
Table.ColumnNames(#"Changed Type"),
IdentifyPlannedColumns
),
CreatePlannedTable =
let
SelColumn = Table.SelectColumns(#"Changed Type", IdentifyPlannedColumns),
RemoveTop1 = Table.Skip(SelColumn,1),
PromoteHeaders = Table.PromoteHeaders(RemoveTop1, [PromoteAllScalars=true]),
RemoveTop2 = Table.Skip(PromoteHeaders,1),
UnpivotOtherThenFirst = Table.UnpivotOtherColumns(RemoveTop2, {""}, "Attribute", "Value"),
TableSelectRows = Table.SelectRows
(
UnpivotOtherThenFirst,
each Value.Is(try Date.From(_[Attribute]) otherwise "", type date) and _[Value]<>""
),
AddPlan = Table.AddColumn(TableSelectRows, "Type", each "Planned")
in
AddPlan,

CreateActualTable =
let
SelColumn = Table.SelectColumns(#"Changed Type", IdentifyActualColumns),
RemoveTop1 = Table.Skip(SelColumn,1),
PromoteHeaders = Table.PromoteHeaders(RemoveTop1, [PromoteAllScalars=true]),
RemoveTop2 = Table.Skip(PromoteHeaders,1),
UnpivotOtherThenFirst = Table.UnpivotOtherColumns(RemoveTop2, {""}, "Attribute", "Value"),
TableSelectRows = Table.SelectRows
(
UnpivotOtherThenFirst,
each Value.Is(try Date.From(_[Attribute]) otherwise "", type date) and _[Value]<>""
),
AddActual = Table.AddColumn(TableSelectRows, "Type", each "Actual")
in
AddActual,

CombineBoth =Table.Combine({CreatePlannedTable, CreateActualTable}),
#"Changed Type1" = Table.TransformColumnTypes(CombineBoth,{{"Value", type number}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[#""]), "", "Value", List.Sum)
in
#"Pivoted Column"

[Jimmy801]

Hello @Mohammada 

if the query #"Mail(2)" is delivering a table from Excel-sheet in your mail-account change the frist step like this

Source = #"Mail(2)",

Be aware that the table deliverd to my code has to have no real column headers, only anonymous "column1" etc.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Mohammada]

Hi Jimmy,

I have tried other way but it didnt work. It gave me the following error.The columns I have in my table have column1,column2 so on not the standard column headers.

Please click below for power bi table:

https://londonenergy-my.sharepoint.com/:x:/g/personal/mohammada_londonenergyltd_com/EbS0L9iwvpxPuiKv... 

Please click here for the original table: https://londonenergy-my.sharepoint.com/:x:/g/personal/mohammada_londonenergyltd_com/EaC5RyxmwwNGurbV...

[Jimmy801]

Hello @Mohammada 

First create a query that produces the output you posted in your first post (anonymous columns...). Call this query "MyTable".

Then create a new blank query and copy paste this code

let
    Source = MyTable
    
    IdentifyColumnWithCellActual = List.Select
    (
        List.Transform
        (
            Table.ColumnNames(Source),
            (trans)=> if List.Count(List.Select(Table.Column(Source, trans),each _ = "ACTUALS" ))>0 then trans else null
        ), 
        each _ <> null
    ){0},
    IdentifyPlannedColumns = List.FirstN
    (
        Table.ColumnNames(Source),
        each _ <> IdentifyColumnWithCellActual
    ),
    IdentifyActualColumns = List.Difference
    (
        Table.ColumnNames(Source),
        IdentifyPlannedColumns
    ),
    CreatePlannedTable = 
    let 
        SelColumn = Table.SelectColumns(Source, IdentifyPlannedColumns),
        RemoveTop1 = Table.Skip(SelColumn,1),
        PromoteHeaders = Table.PromoteHeaders(RemoveTop1, [PromoteAllScalars=true]),
        RemoveTop2 = Table.Skip(PromoteHeaders,1),
        UnpivotOtherThenFirst = Table.UnpivotOtherColumns(RemoveTop2, {""}, "Attribute", "Value"),
        TableSelectRows = Table.SelectRows
        (
            UnpivotOtherThenFirst,
            each Value.Is(try Date.From(_[Attribute]) otherwise "", type date)  and _[Value]<>""
        ),
        AddPlan = Table.AddColumn(TableSelectRows, "Type", each "Planned")
    in 
        AddPlan,
    
    CreateActualTable = 
    let 
        SelColumn = Table.SelectColumns(Source, IdentifyActualColumns),
        RemoveTop1 = Table.Skip(SelColumn,1),
        PromoteHeaders = Table.PromoteHeaders(RemoveTop1, [PromoteAllScalars=true]),
        RemoveTop2 = Table.Skip(PromoteHeaders,1),
        UnpivotOtherThenFirst = Table.UnpivotOtherColumns(RemoveTop2, {""}, "Attribute", "Value"),
        TableSelectRows = Table.SelectRows
        (
            UnpivotOtherThenFirst,
            each Value.Is(try Date.From(_[Attribute]) otherwise "", type date)  and _[Value]<>""
        ),
        AddActual = Table.AddColumn(TableSelectRows, "Type", each "Actual")
    in 
        AddActual,
    
    CombineBoth =Table.Combine({CreatePlannedTable, CreateActualTable}),
    #"Changed Type1" = Table.TransformColumnTypes(CombineBoth,{{"Value", type number}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[#""]), "", "Value", List.Sum)
in
    #"Pivoted Column"

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Mohammada]

Hi Jimmy,

Please see the below error:

Many Thanks,

Mohammad

[Jimmy801]

Hello @Mohammada 

if your data has this format

the code is working fine

this "empty" column name is coming from the cell value in the column1 column second row.

can you select the prior step and send me a screenshot

BR

Jimmy

[Mohammada]

Hi Jimmy,

I have data in this format:

Please see the prior step when i go to the error:

[Jimmy801]

Hello @Mohammada 

this is not the same format as you posted. But anyway... try this code

let
    #"Changed Type" = MyTable,
    
    IdentifyColumnWithCellActual = List.Select
    (
        List.Transform
        (
            Table.ColumnNames(#"Changed Type"),
            (trans)=> if List.Count(List.Select(Table.Column(#"Changed Type", trans),each _ = "ACTUALS" ))>0 then trans else null
        ), 
        each _ <> null
    ){0},
    IdentifyPlannedColumns = List.FirstN
    (
        Table.ColumnNames(#"Changed Type"),
        each _ <> IdentifyColumnWithCellActual
    ),
    IdentifyActualColumns = List.Difference
    (
        Table.ColumnNames(#"Changed Type"),
        IdentifyPlannedColumns
    ),
    CreatePlannedTable = 
    let 
        SelColumn = Table.SelectColumns(#"Changed Type", IdentifyPlannedColumns),
        RemoveTop1 = Table.Skip(SelColumn,2),
        PromoteHeaders = Table.PromoteHeaders(RemoveTop1, [PromoteAllScalars=true]),
        RemoveTop2 = Table.Skip(PromoteHeaders,1),
        UnpivotOtherThenFirst = Table.UnpivotOtherColumns(RemoveTop2, {""}, "Attribute", "Value"),
        TableSelectRows = Table.SelectRows
        (
            UnpivotOtherThenFirst,
            each Value.Is(try Date.From(_[Attribute]) otherwise "", type date)  and _[Value]<>""
        ),
        AddPlan = Table.AddColumn(TableSelectRows, "Type", each "Planned")
    in 
        AddPlan,
    
    CreateActualTable = 
    let 
        SelColumn = Table.SelectColumns(#"Changed Type", IdentifyActualColumns),
        RemoveTop1 = Table.Skip(SelColumn,2),
        PromoteHeaders = Table.PromoteHeaders(RemoveTop1, [PromoteAllScalars=true]),
        RemoveTop2 = Table.Skip(PromoteHeaders,1),
        UnpivotOtherThenFirst = Table.UnpivotOtherColumns(RemoveTop2, {""}, "Attribute", "Value"),
        TableSelectRows = Table.SelectRows
        (
            UnpivotOtherThenFirst,
            each Value.Is(try Date.From(_[Attribute]) otherwise "", type date)  and _[Value]<>""
        ),
        AddActual = Table.AddColumn(TableSelectRows, "Type", each "Actual")
    in 
        AddActual,
    
    CombineBoth =Table.Combine({CreatePlannedTable, CreateActualTable}),
    #"Changed Type1" = Table.TransformColumnTypes(CombineBoth,{{"Value", type number}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[#""]), "", "Value", List.Sum)
in
    #"Pivoted Column"

try this, if its not working, choose the prior step of the pivoted column... and thats it ChangedType1 and send a screenshot

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy