Forum Discussion

sashakorniakUK's avatar
sashakorniakUK
Copper Contributor
Dec 03, 2020

Excel formula function or macro? Data Migration for Horizontal Columns to Vertical Columns

Excel formula function or macro? Data Migration for Horizontal Columns to Vertical Columns   This must be a common problem?    I have old database exported to csv. The Old columns are;   First...
excel
Formulas and Functions
Macros and VBA
sashakorniakUK's avatar
sashakorniakUK
Copper Contributor
Dec 04, 2020

sashakorniakUK adversi 

 

I have been using power query to find a solution

So far I found this works, however I not find my 65000 rows of data has more than 5 names per address. However I need to either add names 6+ to a new row or stop that a source etc.

 

here the code so far

 

let

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"First Name", type text}, {"Surance", type text}, {"DOB", type text}, {"1 address", type text}}),

    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"1 address"}, "Attribute", "Value"),

    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"1 address"}, {{"Count", each _, type table}}),

    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine([Count][Value],",##,")),

    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByDelimiter(",##,", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7", "Custom.8", "Custom.9", "Custom.10", "Custom.11", "Custom.12", "Custom.13", "Custom.14", "Custom.15"}),

    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type text}, {"Custom.3", type datetime}, {"Custom.4", type text}, {"Custom.5", type text}, {"Custom.6", type datetime}, {"Custom.7", type text}, {"Custom.8", type text}, {"Custom.9", type datetime}, {"Custom.10", type text}, {"Custom.11", type text}, {"Custom.12", type datetime}, {"Custom.13", type text}, {"Custom.14", type text}, {"Custom.15", type datetime}}),

    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Count"}),

    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"Custom.3", type date}, {"Custom.6", type date}, {"Custom.9", type date}, {"Custom.12", type date}, {"Custom.15", type date}})

in

    #"Changed Type2"

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 04, 2020

sashakorniakUK 

Same idea, but without hardcoding column names

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(
        Source,
        {"address"},
        {
            {"Tables",
             each
[   #"Removed Columns" = Table.RemoveColumns(_,{"address"}),
    #"Added Index" = Table.AddIndexColumn(
        #"Removed Columns",
        "Index", 1, 1, Int64.Type
    ),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(
        #"Added Index",
        {"Index"}, "Attribute", "Value"
    ),
    #"Merged Columns" = Table.CombineColumns(
        Table.TransformColumnTypes(
            #"Unpivoted Other Columns",
                {{"Index", type text}}, "en-GB"
            ),
            {"Attribute", "Index"},
            Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"
    ),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(
        #"Transposed Table1",
        [PromoteAllScalars=true]
    )
][#"Promoted Headers"]
            },
            {"Count", each Table.RowCount(_), Int64.Type}
        }
    ),
    // Generate Column Names
    MaxRows = List.Max(#"Grouped Rows"[Count]),
 
    RemoveAddress = Table.RemoveColumns(Source,{"address"}),
    #"Kept First Rows" = Table.FirstN(RemoveAddress,1),
    #"Added Custom" = Table.AddColumn(#"Kept First Rows", "Index", each {1..MaxRows}),
    #"Expanded Index" = Table.ExpandListColumn(#"Added Custom", "Index"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded Index", {"Index"}, "Attribute", "Value"),
    GetTogether = Table.CombineColumns(
        Table.TransformColumnTypes(
            #"Unpivoted Other Columns",
            {{"Index", type text}}, "en-GB"),{"Attribute", "Index"},
            Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"ColumnNames"
    ),
    ColumnNames = GetTogether[ColumnNames],
 
 
    #"Expanded Tables" = Table.ExpandTableColumn(#"Grouped Rows", "Tables", ColumnNames)
in
    #"Expanded Tables"

 

  • sashakorniakUK's avatar
    sashakorniakUK
    Copper Contributor
    Dec 04, 2020

    SergeiBaklan  - I cannot download the file?

     

    testing the code shortly.

     

    Any thoughts on the issue with if an address has more than 5 names?

Resources