topic Re: Power Query | Sum Multiple Varied Columns per Row in Power Query

Power Query | Sum Multiple Varied Columns per Row

AMVondra — Sat, 11 Mar 2023 19:20:49 GMT

Dear Colleagues.

I have a task to sum multiple columns (with time durations) amount (will increase) per row. And I didn't know how to do it and didn't find any solutions. My last rows of code are below. Could You please help me with it?

#"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"Maternity Leave", type duration}, {"Annual Leave", type duration}, {"Sick Leave", type duration}}), #"Replaced Value1" = Table.ReplaceValue(#"Changed Type1",null,#duration(0, 0, 0, 0),Replacer.ReplaceValue,{"Maternity Leave", "Annual Leave", "Sick Leave"}),

Thanks in advance 🤝

Re: Power Query | Sum Multiple Varied Columns per Row

serpiva64 — Sun, 12 Mar 2023 09:31:01 GMT

Please, post some sample data

Re: Power Query | Sum Multiple Varied Columns per Row

AMVondra — Mon, 13 Mar 2023 08:52:40 GMT

serpiva64 here is the link https://drive.google.com/file/d/15pAz6BYTMShXJY_h06Z8i3SLcTTWUqaL/view?usp=sharing Thanks a lot!

P.S. I've just added the column with the Total Absence Time to understand how it should be. So the last 2 rows of the code changed a little.

Re: Power Query | Sum Multiple Varied Columns per Row

AMVondra — Wed, 15 Mar 2023 15:36:38 GMT

And really, nobody knows how to resolve it?

Re: Power Query | Sum Multiple Varied Columns per Row

serpiva64 — Wed, 15 Mar 2023 18:27:57 GMT

Hi,

i do not understand well your problem.

If it is to sum some columns of type duration you can do this way

If the problem is another, try to explain again.

Re: Power Query | Sum Multiple Varied Columns per Row

AMVondra — Thu, 16 Mar 2023 09:33:01 GMT

The issue is that amount of columns will be increasing for some time and now there are 35 columns, tomorrow it can be already 40 columns. So I don't know how to sum data from the dynamic range of columns. My data is just an example to understand the structure of data.