Power Query - question

%3CLINGO-SUB%20id%3D%22lingo-sub-2776396%22%20slang%3D%22en-US%22%3EPower%20Query%20-%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2776396%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20have%20a%20problem%20with%20the%20following%20issue%3A%3CBR%20%2F%3EI%20have%20several%20tabs%20with%20the%20same%20header%20(data%20varies)%3A%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22125%22%3E%3CP%3ETitle%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3ETerritory%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3EApril%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3EMay%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3EJune%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22125%22%3E%3CP%3EX%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3EA%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3E1%20USD%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3E4%20USD%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3E7%20USD%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22125%22%3E%3CP%3EXX%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3EB%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3E2%20USD%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3E5%20USD%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3E8%20USD%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22125%22%3E%3CP%3EXXX%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3Ec%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3E3%20USD%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3E6%20USD%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3E9%20USD%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20need%20to%20create%20the%20following%3A%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22125%22%3E%3CP%3ETitle%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3ETerritory%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3EDate%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3EValues%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22125%22%3E%3CP%3EX%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3EA%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3EApril%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3E1%20USD%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22125%22%3E%3CP%3EXX%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3EB%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3EApril%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3E2%20USD%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22125%22%3E%3CP%3EXXX%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3Ec%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3EApril%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3E3%20USD%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22125%22%3E%3CP%3EX%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3EA%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3EMay%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3E4%20USD%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22125%22%3E%3CP%3EXX%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3EB%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3EMay%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3E5%20USD%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22125%22%3E%3CP%3EXXX%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3Ec%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3EMay%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3E6%20USD%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22125%22%3E%3CP%3EX%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3EA%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3EJune%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3E7%20USD%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22125%22%3E%3CP%3EXX%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3EB%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3EJune%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3E8%20USD%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22125%22%3E%3CP%3EXXX%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3Ec%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3EJune%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22125%22%3E%3CP%3E9%20USD%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20very%20new%20to%20Power%20Query.%20I%20would%20appreciate%20if%20someone%20could%20explain%20the%20steps%20I%20need%20to%20take%20in%20order%20to%20get%20the%20second%20table.%3CBR%20%2F%3EThank%20you!%3CBR%20%2F%3EGabriella%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2776396%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2776631%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2776631%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1163491%22%20target%3D%22_blank%22%3E%40GabriellaFerenc%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStep%201%3A%20This%20is%20your%20data%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yea_So_0-1632354194280.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312273i61FD4B2F9CE0ABB6%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Yea_So_0-1632354194280.png%22%20alt%3D%22Yea_So_0-1632354194280.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EStep%202%3A%20Select%20any%20cell%20in%20your%20data%20range%20then%20click%20Data%26gt%3BFrom%20Table%2FRange%20then%20click%20ok%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yea_So_3-1632354388812.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312276i6868D74CF4F4E4AF%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Yea_So_3-1632354388812.png%22%20alt%3D%22Yea_So_3-1632354388812.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Eyou%20get%20this%20screen%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yea_So_4-1632354464538.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312277iC1E86EDDC2B337C6%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Yea_So_4-1632354464538.png%22%20alt%3D%22Yea_So_4-1632354464538.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EStep%203%3C%2FP%3E%3CP%3ESelect%20the%20Title%20and%20Territory%20columns%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yea_So_5-1632354788635.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312278iCD1478D2BFD54FD4%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Yea_So_5-1632354788635.png%22%20alt%3D%22Yea_So_5-1632354788635.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ERight%20click%20the%20selected%20columns%20then%20select%20Unpivot%20other%20columns%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yea_So_6-1632354860180.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312279i12BE4E845E5FF13C%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Yea_So_6-1632354860180.png%22%20alt%3D%22Yea_So_6-1632354860180.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Eand%20viola%2C%20now%20all%20you%20have%20to%20do%20is%20rename%20the%20Attribute%20column%20to%20dates%20by%20double%20clicking%20on%20the%20column%20header%20Attribute%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yea_So_7-1632354915824.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312280i667CDA1FCE415124%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Yea_So_7-1632354915824.png%22%20alt%3D%22Yea_So_7-1632354915824.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yea_So_8-1632355022748.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312281iD71F3ECA26AA4A02%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Yea_So_8-1632355022748.png%22%20alt%3D%22Yea_So_8-1632355022748.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThen%20click%20close%20and%20load%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yea_So_9-1632355076015.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312282i5E676D2BE28C4D94%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Yea_So_9-1632355076015.png%22%20alt%3D%22Yea_So_9-1632355076015.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Echeers%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Visitor

Hi, I have a problem with the following issue:
I have several tabs with the same header (data varies):

Title

Territory

April

May

June

X

A

1 USD

4 USD

7 USD

XX

B

2 USD

5 USD

8 USD

XXX

c

3 USD

6 USD

9 USD

 

I would need to create the following: 

Title

Territory

Date

Values

X

A

April

1 USD

XX

B

April

2 USD

XXX

c

April

3 USD

X

A

May

4 USD

XX

B

May

5 USD

XXX

c

May

6 USD

X

A

June

7 USD

XX

B

June

8 USD

XXX

c

June

9 USD

 

I am very new to Power Query. I would appreciate if someone could explain the steps I need to take in order to get the second table.
Thank you!
Gabriella

1 Reply

@GabriellaFerenc 

 

Step 1: This is your data

Yea_So_0-1632354194280.png

Step 2: Select any cell in your data range then click Data>From Table/Range then click ok

Yea_So_3-1632354388812.png

you get this screen:

Yea_So_4-1632354464538.png

Step 3

Select the Title and Territory columns

Yea_So_5-1632354788635.png

Right click the selected columns then select Unpivot other columns:

Yea_So_6-1632354860180.png

and viola, now all you have to do is rename the Attribute column to dates by double clicking on the column header Attribute

Yea_So_7-1632354915824.png

Yea_So_8-1632355022748.png

Then click close and load

Yea_So_9-1632355076015.png

 

 

cheers