Power Query: Help transposing/pivoting data set to correct records

%3CLINGO-SUB%20id%3D%22lingo-sub-1037148%22%20slang%3D%22en-US%22%3EPower%20Query%3A%20Help%20transposing%2Fpivoting%20data%20set%20to%20correct%20records%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1037148%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20dataset%20that%20looks%20like%20this%3A%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3CSTRONG%3Erecord%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3ECode%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EValue%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3Ev1%3C%2FTD%3E%3CTD%3ES%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3Ev2%3C%2FTD%3E%3CTD%3E23%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3Ev3%3C%2FTD%3E%3CTD%3EPower%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3Ev1%3C%2FTD%3E%3CTD%3EP%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3Ev2%3C%2FTD%3E%3CTD%3E25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3Ev3%3C%2FTD%3E%3CTD%3EPower%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3Ev4%3C%2FTD%3E%3CTD%3E23%2F12%2F2018%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3Ev1%3C%2FTD%3E%3CTD%3ES%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3Ev3%3C%2FTD%3E%3CTD%3EGas%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3Ev4%3C%2FTD%3E%3CTD%3E16%2F04%2F2018%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20three%20records%20(as%20per%20column%201%20-%20record)%2C%204%20columns%20(v1%20to%204%20in%26nbsp%3B%20column%202%20-%20Code)%20and%20the%20value%20for%20that%20column%2Frecord%20(column%203%20Value)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20output%20should%20look%20like%20this%3A%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3CSTRONG%3Erecord%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3Ev1%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3Ev2%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3Ev3%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3Ev4%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3ES%3C%2FTD%3E%3CTD%3E23%3C%2FTD%3E%3CTD%3EPower%3C%2FTD%3E%3CTD%3Enull%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3EP%3C%2FTD%3E%3CTD%3E25%3C%2FTD%3E%3CTD%3EPower%3C%2FTD%3E%3CTD%3E23%2F12%2F2018%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3ES%3C%2FTD%3E%3CTD%3Enull%3C%2FTD%3E%3CTD%3EGas%3C%2FTD%3E%3CTD%3E16%2F04%2F2018%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20make%20this%20transformation%20in%20%3CU%3EPower%20Query%3C%2FU%3E%20as%20the%20record%20set%20is%20large%20and%20frequently%20changes.%26nbsp%3B%20I%20know%20how%20to%20load%20it%20into%20query%20but%20can't%20workout%20how%20to%20make%20a%20distinct%20list%20from%20%5BCode%5D%20as%20column%20headers%20and%20then%20apply%20the%20%5Bvalue%5D%20as%20the%20rows%20under%20those%20columns.%26nbsp%3B%20Note%2C%20that%20not%20every%20column%20appears%20in%20each%20record%20so%20can't%20just%20assume%20the%20first%20value%20in%20the%20record%20is%20v1%20and%20the%20second%20is%20v2.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20for%20your%20assistance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1037148%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1037219%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%3A%20Help%20transposing%2Fpivoting%20data%20set%20to%20correct%20records%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1037219%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F471110%22%20target%3D%22_blank%22%3E%40SimonFA%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20pivoting%20that%20will%20be%20done%20by%20default%2C%20generated%20script%20is%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Table1%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20%23%22Changed%20Type%22%20%3D%20Table.TransformColumnTypes(%0A%20%20%20%20%20%20%20%20Source%2C%0A%20%20%20%20%20%20%20%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%7B%22record%22%2C%20Int64.Type%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%7B%22Code%22%2C%20type%20text%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%7B%22Value%22%2C%20type%20any%7D%0A%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20)%2C%0A%20%20%20%20%23%22Pivoted%20Column%22%20%3D%20Table.Pivot(%0A%20%20%20%20%20%20%20%20%23%22Changed%20Type%22%2C%0A%20%20%20%20%20%20%20%20List.Distinct(%23%22Changed%20Type%22%5BCode%5D)%2C%0A%20%20%20%20%20%20%20%20%22Code%22%2C%0A%20%20%20%20%20%20%20%20%22Value%22)%0Ain%0A%20%20%20%20%23%22Pivoted%20Column%22%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Efor%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20629px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F159241i5143D14EE720DFAB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I have a dataset that looks like this:

recordCodeValue
1v1S
1v223
1v3Power
2v1P
2v225
2v3Power
2v423/12/2018
3v1S
3v3Gas
3v416/04/2018

 

This is three records (as per column 1 - record), 4 columns (v1 to 4 in  column 2 - Code) and the value for that column/record (column 3 Value)

 

The output should look like this:

recordv1v2v3v4
1S23Powernull
2P25Power23/12/2018
3SnullGas16/04/2018

 

I need to make this transformation in Power Query as the record set is large and frequently changes.  I know how to load it into query but can't workout how to make a distinct list from [Code] as column headers and then apply the [value] as the rows under those columns.  Note, that not every column appears in each record so can't just assume the first value in the record is v1 and the second is v2.

 

Thanks in advance for your assistance.

1 Reply
Highlighted

@SimonFA 

With pivoting that will be done by default, generated script is

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(
        Source,
        {
            {"record", Int64.Type},
            {"Code", type text},
            {"Value", type any}
        }
    ),
    #"Pivoted Column" = Table.Pivot(
        #"Changed Type",
        List.Distinct(#"Changed Type"[Code]),
        "Code",
        "Value")
in
    #"Pivoted Column"

for

image.png