PowerQuery Function: Unpivot Data

[jajatidev]

Hello,
I have pivoted monthly forecast files stored in a SharePoint folder.
To unpivot the data I created the following PQ Function. However, I'm getting the following error message while trying to pass the files.

Can someone please guide me on how to overcome the issue?

(MyTable as table) =>
let
    // Filter out hidden files
    #"Filtered Hidden Files1" = Table.SelectRows(MyTable, each [Attributes]?[Hidden]? <> true),
    
    // Add a custom column to transform the binary content to a table
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each Csv.Document(Binary.Decompress([Content], Compression.Deflate))),
    
    // Rename columns for clarity
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    
    // Remove unnecessary columns
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (2)"}),
    
    // Expand the table column to get the data
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),
    
    // Promote headers
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Table Column1", [PromoteAllScalars=true]),
    
    // Get the first column name for renaming
    CycleForecast = Table.ColumnNames(#"Promoted Headers"){0},
    
    // Rename the first column to "Forecast_Month"
    ForecastMonth = Table.RenameColumns(#"Promoted Headers", {{CycleForecast, "Forecast_Month"}}),
    
    // Replace specific values in the "Forecast_Month" column
    #"Replaced Value" = Table.ReplaceValue(ForecastMonth, "Monthly_Direct_Forecast_Hardware_", "", Replacer.ReplaceText, {"Forecast_Month"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value", ".csv", "", Replacer.ReplaceText, {"Forecast_Month"}),
    
    // Unpivot the other columns
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value1", {"Forecast_Month", "Business Area", "SKU No.", "PL"}, "Attribute", "Value"),
    
    // Change the data types of specific columns
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Value", Int64.Type}, {"PL", type text}, {"SKU No.", type text}, {"Business Area", type text}})
in
    #"Changed Type"

 

[JEC]

Converting binary to excel would be something like (instead of your second step).

Change the table name too in the code below. I used myTable but that should be the previous step

trform = List.Transform(myTable[Content], (x)=> Excel.Workbook(x))

 

[jajatidev]

Converting binary to excel would be something like (instead of your second step).

Change the table name too in the code below. I used myTable but that should be the previous step

trform = List.Transform(myTable[Content], (x)=> Excel.Workbook(x))

Hello JEC,
New Year Greeting! I hope you had a great time with your family and friends.

Thank you for getting back to me. I tried as advised however, I am still faced with the same error.

As mentioned, I'm trying to create a function that can be used for parsing multiple workbooks and then expand for the final output. To achieve this I'm adding a custom column with the following formula

Unpivot([Content]

(myTable as table) =>
let
    trform = List.Transform(myTable[Content], (x) => Excel.Workbook(x)),
    #"Invoke Custom Function1" = Table.AddColumn(trform, "Transform File (2)", each #"Transform File (2)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (2)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Table Column1", [PromoteAllScalars=true]),
    CycleForecast = Table.ColumnNames (#"Promoted Headers"){0},
    ForecastMonth = Table.RenameColumns (#"Promoted Headers", {{CycleForecast, "Forecast_Month"}}),
    #"Replaced Value" = Table.ReplaceValue(ForecastMonth,"Monthly_Direct_Forecast_Hardware_","",Replacer.ReplaceText,{"Forecast_Month"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",".csv","",Replacer.ReplaceText,{"Forecast_Month"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value1", {"Forecast_Month", "Business Area", "SKU No.", "PL"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", Int64.Type}, {"PL", type text}, {"SKU No.", type text}, {"Business Area", type text}})
in
    #"Changed Type"

 

[AlienSx]

@jajatidev , your original function works with table as parameter. Such table must have a column Content with binary values (#"Removed Columns" table in your image).
You are trying to invoke your function via user interface as Add Column >> Invoke Custom Function. This not going to work because Table.AddColumn passes each instance of Content column into your function which is binary. That's why you get "can't convert value of type Binary to type Table" error.
I don't know how to help you using UI only, sorry.
p.s. Excel.Workbook has nothing to do with your data (csv files).

 

[jajatidev]

@jajatidev , your original function works with table as parameter. Such table must have a column Content with binary values (#"Removed Columns" table in your image).
You are trying to invoke your function via user interface as Add Column >> Invoke Custom Function. This not going to work because Table.AddColumn passes each instance of Content column into your function which is binary. That's why you get "can't convert value of type Binary to type Table" error.
I don't know how to help you using UI only, sorry.
p.s. Excel.Workbook has nothing to do with your data (csv files).

Hello AlienSx,
New Year Greetings! Thanks for the feedback.

The CSV files I'm dealing with are published and saved in a SharePoint Folder. The list of reports is expected to grow over time. Hence, I'm trying to create this function to unpivot and append all files available in the SharePoint folder before I can begin massaging the data. Do you have any suggestions on how to approach this?

Here is the initial query I'm trying to convert into a dynamic function that can be applied to all the files in the specific folder so that I do not have to repeat the process.

let
    Source = SharePoint.Files("SharePointLink", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "SharePointLink") and ([Name] = "Monthly_Forecast_May-24.csv")),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Table Column1", [PromoteAllScalars=true]),
    SourceName = Table.ColumnNames (#"Promoted Headers"){0},
    RenameHeader = Table.RenameColumns (#"Promoted Headers",{{SourceName, "Forecast_Cycle"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(RenameHeader, {"Forecast_Cycle", "SKU No.", "PL"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

Here is the sample data from a couple of files for your reference. I hope this will facilitate a solution.

Monthly_Forecast_May-24.csv
	A	B	C	D	E	F	G	H	I	J	K
1	SKU No.	PL	Jun-25	Jul-25	Aug-25	Sep-25	Oct-25	Nov-25	Dec-25	Jan-25	Feb-25
2	1NR73A-K1H	AP	318	50	50	50	50	50	50	0	0
3	A1A30A-KGJ	AL	318	50	50	50	50	50	50	0	0
4	5RA32F-KGJ	AL	312	318	311	321	343	320	233	207	207
5	A1A77A-KGJ	AL	2984	1408	1648	1973	1454	1267	1157	0	0
6	N7L37A	ST	350	172	138	144	131	79	62	53	32
7	2Z600F-KGJ	AL	597	360	318	356	263	196	167	171	165
8	3JA05AN-150	AN	388	59	31	25	26	14	16	26	30
9	A1A56A-K1H	AP	379	183	96	114	101	89	73	70	79
10	2Z613F-K1H	AP	697	175	121	115	107	99	93	68	68
11	3GZ25A-K1H	AP	3	3	3	3	3	3	3	3	3
12	K5L35A	ST	1930	1390	1053	838	797	602	578	295	291
13	Y3D03A	ST	44	28	9	5	3	3	1	12	10
14	Z7C07A	ST	1807	1152	1130	1295	1099	228	226	182	144
15	3PZ35A-150	AN	294	230	334	228	186	103	171	26	28
16	D7P27A	ST	597	360	318	356	263	196	167	171	165
Monthly_Forecast_May-24

Monthly_Forecast_Jun-24.csv
	A	B	C	D	E	F	G	H	I	J	K	L
1	SKU No.	PL	Jun-25	Jul-25	Aug-25	Sep-25	Oct-25	Nov-25	Dec-25	Jan-25	Feb-25	Mar-25
2	1NR73A-K1H	AP	50	353	50	50	50	50	50	0	0	0
3	A1A30A-KGJ	AL	50	353	50	50	50	50	50	0	0	0
4	5RA32F-KGJ	AL	53	60	284	244	310	238	282	363	487	249
5	A1A77A-KGJ	AL	1075	2852	1648	1973	1454	1267	1157	0	0	0
6	N7L37A	ST	168	143	129	117	143	59	48	17	13	13
7	2Z600F-KGJ	AL	268	318	112	128	116	104	88	84	93	93
8	3JA05AN-150	AN	379	394	31	60	27	14	16	26	30	28
9	A1A56A-K1H	AP	22	744	103	256	118	30	134	70	150	91
10	2Z613F-K1H	AP	88	432	95	99	102	100	101	91	91	91
11	3GZ25A-K1H	AP	3	0	0	0	0	0	0	0	0	0
12	K5L35A	ST	1994	2199	1074	875	763	670	722	328	318	310
13	Y3D03A	ST	110	77	16	102	14	14	13	13	13	13
14	Z7C07A	ST	1441	1756	1311	1462	1267	233	227	344	309	309
15	3PZ35A-150	AN	228	314	342	202	84	177	279	3	6	32
16	D7P27A	ST	1374	2297	1090	1012	1002	645	700	724	656	707
Monthly_Forecast_Jun-24

 

[jajatidev]

Use this data for June instead of the one provided earlier

Monthly_Forecast_Jun-24.csv
	A	B	C	D	E	F	G	H	I	J	K
1	SKU No.	PL	Jul-25	Aug-25	Sep-25	Oct-25	Nov-25	Dec-25	Jan-25	Feb-25	Mar-25
2	1NR73A-K1H	AP	353	50	50	50	50	50	0	0	0
3	A1A30A-KGJ	AL	353	50	50	50	50	50	0	0	0
4	5RA32F-KGJ	AL	60	284	244	310	238	282	363	487	249
5	A1A77A-KGJ	AL	2852	1648	1973	1454	1267	1157	0	0	0
6	N7L37A	ST	143	129	117	143	59	48	17	13	13
7	2Z600F-KGJ	AL	318	112	128	116	104	88	84	93	93
8	3JA05AN-150	AN	394	31	60	27	14	16	26	30	28
9	A1A56A-K1H	AP	744	103	256	118	30	134	70	150	91
10	2Z613F-K1H	AP	432	95	99	102	100	101	91	91	91
11	3GZ25A-K1H	AP	0	0	0	0	0	0	0	0	0
12	K5L35A	ST	2199	1074	875	763	670	722	328	318	310
13	Y3D03A	ST	77	16	102	14	14	13	13	13	13
14	Z7C07A	ST	1756	1311	1462	1267	233	227	344	309	309
15	3PZ35A-150	AN	314	342	202	84	177	279	3	6	32
16	D7P27A	ST	2297	1090	1012	1002	645	700	724	656	707
Monthly_Forecast_Jun-24

 

[AlienSx]

@jajatidev , completely blind solution (I don't use SP). Try this. If it works then you can create a function out of it with a path to SP folder as a parameter

let
    unpivot_csv = (bin as binary) => Table.UnpivotOtherColumns(
        Table.PromoteHeaders(Csv.Document(bin)),
        {"SKU No.", "PL"},
        "Attribute",
        "Value"
    ),
    row_files = SharePoint.Files("SharePointLink", [ApiVersion = 15]),
    remove_hidden = Table.SelectRows(row_files, each [Attributes]?[Hidden]? <> true)[[Content], [Name]],
    transormations = Table.TransformColumns(
        remove_hidden,
        {
            {"Content", unpivot_csv},
            {"Name", (x) => Text.BetweenDelimiters(x, "_", ".", {0, RelativePosition.FromEnd})}
        }
    ),
    renames = Table.RenameColumns(transformations, {{"Name", "Forecast_Month"}}),
    expand = Table.ExpandTableColumn(renames, "Content", {"SKU No.", "PL", "Attribute", "Value"})
in
    expand

You should use Advanced Editor to create a blank query and paste this code. I don't know what "SharePointLink" is . Must be some URL...