Transposing data from excel

[danielbacher]

I have a problem with a very difficult dataset. This dataset has year written over 4 columns and all the values are therefore written after as four different columns. (See Screenshot)
"Nyeste år" = Newest Year, and the -1, -2, -3 is years back.
Every row is one company's data.

I don't see a way to work with this setup in Power BI and can't figure out if there is a way to transpose some of the columns.

My goal is to have 4 rows for every company (1 row for each year) so the values are setup by rows and not columns.
Is there even a way to do this?

[dufoq3]

Hi, it is possible, but you have to provide sample data in usable format - not as a screenshot (if you don't know how - read my note below this post) and also expected result based on sample data.

At the beginning I've created some query for you (both files attached - just replace addres in Source step).

Before

After

[danielbacher]

Yes sry for the late respons. Here is an example file from the dataset I'm working on, with expected result.
https://fld365-my.sharepoint.com/:x:/g/personal/daniel_fld_dk/EYfXvXdoiUFNknz_FRR7OVEB4vC-t0VekYo93s... 
The problem I'm having is I have to transpose some of the columns, but not all of them. The columns that should not be transposed doesn't matter if the values in them are duplicated to all rows for each company or they are a single value with the rest of the years blank.
I only have to transpose the years and all the values after that.

[dufoq3]

Make the link public

[danielbacher]

Example file.xlsx

Here we go

[dufoq3]

Try this:

let
    Source = Excel.Workbook(File.Contents("C:\Users\Downloads\PowerQueryForum\danielbacher\Example file.xlsx"), null, true),
    #"Sample Data_Sheet" = Source{[Item="Sample Data",Kind="Sheet"]}[Data],
    KeptFirstRows = Table.FirstN(#"Sample Data_Sheet", each [Column1] <> "Expected Result"),
    RemovedBlankRows = Table.SelectRows(KeptFirstRows, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null, "Data Sample"}))),
    PromotedHeaders = Table.PromoteHeaders(RemovedBlankRows, [PromoteAllScalars=true]),
    RemovedBlankColumns = List.Accumulate(Table.ColumnNames(PromotedHeaders), PromotedHeaders, (s,c)=> if List.IsEmpty(List.RemoveNulls(Table.Column(s, c))) then Table.RemoveColumns(s, c) else s),
    StepBack = RemovedBlankColumns,
    #"Demoted Headers" = Table.DemoteHeaders(StepBack),
    TransposedTable = Table.Transpose(#"Demoted Headers"),
    MergedColumns = Table.CombineColumns(Table.TransformColumnTypes(TransposedTable, {{"Column2", type text}}, "sk-SK"),{"Column1", "Column2"},Combiner.CombineTextByDelimiter("||", QuoteStyle.None),"Merged"),
    TransposedTable1 = Table.Transpose(MergedColumns),
    PromotedHeaders1 = Table.PromoteHeaders(TransposedTable1, [PromoteAllScalars=true]),
    AddedIndex = Table.AddIndexColumn(PromotedHeaders1, "Index", 0, 1, Int64.Type),
    UnpivotedOtherColumns = Table.UnpivotOtherColumns(AddedIndex, List.Select(Table.ColumnNames(AddedIndex), each not List.Contains({"-3", "-2", "-1", "Nyeste år"}, _, (x,y)=> Text.EndsWith(y, x))), "Attribute", "Value"),
    SplitColumnByDelimiter = Table.SplitColumn(UnpivotedOtherColumns, "Attribute", Splitter.SplitTextByDelimiter("||", QuoteStyle.Csv), {"Attribute", "Year"}),
    ExtractedTextBeforeDelimiter = Table.TransformColumns(SplitColumnByDelimiter, {{"Attribute", each if Text.Contains(_, "_") then Text.BeforeDelimiter(_, "_", {0, RelativePosition.FromEnd}) else _, type text}}),
    ReplacedValue = Table.ReplaceValue(ExtractedTextBeforeDelimiter,"N/A",null,Replacer.ReplaceValue,{"Value"}),
    PivotedColumn = Table.Pivot(ReplacedValue, List.Distinct(ReplacedValue[Attribute]), "Attribute", "Value"),
    ReplacedValue1 = Table.ReplaceValue(PivotedColumn,"Nyeste år","0",Replacer.ReplaceText,{"Year"}),
    ChangedType = Table.TransformColumnTypes(ReplacedValue1,{{"Year", Int64.Type}}),
    SortedRows = Table.Sort(ChangedType,{{"Index", Order.Ascending}, {"Year", Order.Ascending}}),
    RemovedColumns = Table.RemoveColumns(SortedRows,{"Index", "Year"}),
    RenamedColumns = Table.TransformColumnNames(RemovedColumns, each Text.Remove(_, "|")),
    // Set only number or text type
    ChangedTypeDynamic = List.Accumulate(Table.ColumnNames(RenamedColumns), RenamedColumns, (s,c)=>
        if List.AllTrue(List.Transform(List.RemoveNulls(Table.Column(s, c)), (x)=> try (Number.From(x, "en-US") is number) otherwise false))  
        then Table.TransformColumnTypes(s,{{c, type number}}, "en-US")
        else Table.TransformColumnTypes(s,{{c, type text}}) )
in
    ChangedTypeDynamic

[Anonymous]

Hi @danielbacher ,

Did dufoq3's answer help? If so please help mark it as a solution, this time increase the weight when searching for similar questions, thus helping more people, thanks~

Best Regards,
Gao
Community Support Team