CODEWITHSUNDEEP
excelpowerquery
Syzmik
Syzmik

Reputation: 39

Assistance with Power Query to select rows and transpose

I have close to 400 excel files that contain client pricing information. The piece of work is to transform these into multiple database tables to load into Azure SQL Database. I'm tasked with the transformation.

The existing data follows a level of logic. 1 workbook per client. A single client can have multiple ICP's (shown in the example). What I'm looking for is some assistance to use Power Query to transform the data from the source to target formats. Once in Target, it'll be loaded in bulk to SQL server to perform additional transformation into required SQL tables.

Rows that need to be selected

Example of source and target

Source and Target

Having to post as picture as I keep getting an error message about code not being formatted correctly, yet there is no code. Only two tables

Upvotes: 1

Views: 154

Answers (1)

Umut K
Umut K

Reputation: 1388

first index the your table

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
rowscount = List.Count(List.Distinct(Source[type])),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"old rate", Percentage.Type}, {"new rate", Percentage.Type}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"type", "detail", "old rate", "new rate"}),
#"Added Index" = Table.AddIndexColumn(#"Replaced Value", "Index", 0, 1, Int64.Type),
#"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, rowscount), Int64.Type}})
enter code here
in 
#"Integer-Divided Column"

then you can pivot and merge the other data

let
    Source = Table1,
    #"Removed Columns" = Table.RemoveColumns(Source,{"old rate", "new rate"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([type] = "customer name" or [type] = "ICP" or [type] = "supply address")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[#"type"]), "type", "detail"),
    #"Merged Queries" = Table.NestedJoin(#"Pivoted Column", {"Index"}, Table1, {"Index"}, "Table1", JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"type", "old rate", "new rate"}, {"type", "old rate", "new rate"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Table1", each ([type] = "Anytime" or [type] = "Day" or [type] = "EA Levy")),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Index"})
in
    #"Removed Columns1"

see also sample file sample file

data

result

Upvotes: 1

Related Questions