topic Re: Dynamic Unpivot in PowerQuery in Developer

Dynamic Unpivot in PowerQuery

WV — Wed, 01 Mar 2023 22:17:03 GMT

Hi,

I have a table populating from SharePoint list. In this table I have a set columns in a tabular format. I have added a screen shot for reference. I am trying to unpivot the data so that there is one Date column, one Capital column, one Notes column, and one Expense column. Right now there are 26 of each. They also keep adding new SP columns so I would like it to be dynamic so it always automatically captures the new data.

Here is where I have started:

let
Source = SharePoint.Tables("http://inside/private/CAM/", [ApiVersion = 15]),
#"0cb52d41-3597-476f-b8eb-e2020540e973" = Source{[Id="0cb52d41-3597-476f-b8eb-e2020540e973"]}[Items],
#"Unpivoted Columns" = Table.Unpivot(Source, List.Select(Table.ColumnNames(Source), each (Text.StartsWith("SP") and Text.EndsWith("Date"))), "Attribute", "Value")
in
#"Unpivoted Columns"

BUT I am getting this error:

Expression.Error: 1 arguments were passed to a function which expects between 2 and 3.
Details:
Pattern=
Arguments=[List]

I need to make a condtional unpivot saying if the column starts with SP and Ends with Date - then put that into one date column, then do the same for Expense, Notes, and Capital.

Thank you for any and all support.

Re: Dynamic Unpivot in PowerQuery

lbendlin — Sat, 04 Mar 2023 02:35:34 GMT

When you unpivot you need to specify at least one fixed point - a column that does not change.

Re: Dynamic Unpivot in PowerQuery

WV — Mon, 06 Mar 2023 22:35:44 GMT

Where in the syntax would I specify that?

Source = SharePoint.Tables("http://inside/private/CAM/", [ApiVersion = 15]),
#"0cb52d41-3597-476f-b8eb-e2020540e973" = Source{[Id="0cb52d41-3597-476f-b8eb-e2020540e973"]}[Items],
#"Unpivoted Columns" = Table.Unpivot(Source, List.Select(Table.ColumnNames(Source), each (Text.StartsWith("SP") and Text.EndsWith("Date"))), "Attribute", "Value")
in
#"Unpivoted Columns"

Thanks,

Wendy

Re: Dynamic Unpivot in PowerQuery

lbendlin — Tue, 07 Mar 2023 00:02:45 GMT

Please provide a sample of your SP list and indicate the expected result.