Answered

Challenge Extended: Transposing data but in a weird way

(0) Share

Report

Posted on by simonPowerBI

I have a resourcing sheet that is regularly populated and automatically to transpose it in to a different format regularly. Its more complicated than normal Transposing from what I can tell.

My input is like TableA and I need to change it to TableB. The only real difference is the Date section. Changing it from columns to cells. Help!!

TableA - Input

Team	ProjectName	UserName	1-Jan-22	1-Feb-22
TeamA	Project1	User1	0.5	0.3
TeamA	Project1	User2	0.6	0.2

TableB - Desired automated output

Team	ProjectName	UserName	Date	Allocation
TeamA	Project1	User1	1-Jan-22	0.5
TeamA	Project1	User1	1-Feb-22	0.3
TeamA	Project1	User2	1-Jan-22	0.6
TeamA	Project1	User2	1-Feb-22	0.2

Categories:

Power Automate Desktop

I have the same question (0)

All responses (4)

Answers (1)

Sort by

MichaelAnnis 5,727 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

The fastest way to do this would be Excel VBA. Then pad can use the VBA to do it. It’d be much faster than writing each cell one at a time.

Was this reply helpful? Yes No
MichaelAnnis 5,727 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Out of curiosity, did you decide on a workaround for this?

Was this reply helpful? Yes No
Verified answer

XRQ57 206 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi
Using Power Query, you can do it faster.

Load your table in power Query
- Select all your columns containing the date (Jan and Feb....)
- unpivot the columns (French traduction sorry)
and that is it.
Here's the detail of the request (in french)
let
Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
#"Type modifié" = Table.TransformColumnTypes(Source,{{"Team", type text}, {"ProjectName", type text}, {"UserName", type text}, {"01-janv-22", type any}, {"1-Feb-22", type any}}),
#"Tableau croisé dynamique des colonnes supprimé" = Table.UnpivotOtherColumns(#"Type modifié", {"Team", "ProjectName", "UserName"}, "Attribut", "Valeur")
in
#"Tableau croisé dynamique des colonnes supprimé"

Was this reply helpful? Yes No
simonPowerBI 4 on at

Like
a
(0)

Report
Copy link

Link copied!

Merci beaucoup pour votre aide

Was this reply helpful? Yes No