is there any option for columns adding dynamically to pivot Table?????

[avulasandeep]

HI developers ,

Here is my question is that related to the Unpivot the Columns ., 

is there an option for the dynamically adding a new column to the already existing unpivot columns ???

if YES Can you tell the process how to add new columns dynamically? when we refresh the data the new column should add exiting unpivot column. 

here i found one link about this for me it is not worked is there any options??  

please click the link for more details.

Thanks 

sandeep kumar 

[v-jiascu-msft]

Hi Sandeep,

Did you mean the unpivot table function in the Query Editor? Maybe you can try the type "Unpivot other columns" though there could be some errors like “Type isn't the same". You can solve it by changing the type. Please refer to the snapshot below.

Best Regards,
Dale

[avulasandeep]

Hi Dale, 

what I am asking exactly is

"How to add the columns automatically to the Pivot Column when we add new columns in the database or excel ". 

as shown in below 

Hope you understood.

Thanks

Sandeep 

[v-jiascu-msft]

Hi Sandeep,

So you need to refresh the query. When the new columns come in, it will be added into the unpivot columns automatically. 

Best Regards,
Dale

[avulasandeep]

Yes Dale , 

What you said is correct... when we refresh the data the new columns has to populated into the Unpivot Columns.

here i found one link below that we can do in the M script but  i am getting an error.

https://www.oraylis.de/blog/power-query-how-to-unpivot-a-dynamic-number-of-columns

Thanks 

sandeep

[v-jiascu-msft]

So what's the error message?

Best Regards,
Dale

[avulasandeep]

HI Dale , 

Firstly I applied step by step as shown in this below link:

https://www.oraylis.de/blog/power-query-how-to-unpivot-a-dynamic-number-of-columns

Here are my screenshots for the adding the columns to the Pivot table as shown in the below.

Finally, I am getting same as the first step  as the source of the table but the output  for this steps is not reflecting as shown in this link  

https://www.oraylis.de/blog/power-query-how-to-unpivot-a-dynamic-number-of-columns

Can Anyone rectify this error? please let me know

Advance Thanks 

Thanks and Regards

Sandeep 

[v-jiascu-msft]

Hi Sandeep,

You extracted the wrong names of columns. The actual data starts from step 2.  Please refer to the snapshot below.

let
    Source = Excel.Workbook(File.Contents("D:\data_source\ds.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Name_Site", type text}, {"Name_Event", type text}, {"Date", type date}}),
    Cols = Table.ColumnNames(Table1_Table),
    ColsUnPivot = List.Skip(Cols, 1),
    Result = Table.Unpivot(Table1_Table, ColsUnPivot, "A", "Value")
in
    Result

If you still have issues, please share the pbix file and the Excel workbook.

Best Regards,
Dale

[LivioLanzo]

If you use the 'Unpivot Other Columns' feature then you just need to select the columns you do NOT wish to unpivot and all the rest will be dynamically unpivoted @avulasandeep

[LivioLanzo]

It is possible, you can get a dynamic list of column names by using Table.ColumnNames function and remove the column name you do not want to unpivot by using List.RemoveMatchingItems

[avulasandeep]

hi LivioLanzo,

can you please explain this showing with an example where to keep this code at starting to ending.

Thanks

sandeep 

[LivioLanzo]

@avulasandeep

actually this is easier than what I described above. Did you try using the 'Unpivot Other Columns' feature instead of 'Pivot Columns' ?