Transformation Help Required in Power Query

[Imagauthamam]

I have a data in a certain format and it needs to be converted properly in Power query editor.
I tried using unpivot, Transpose and pivot operations.
But somehow im not able to transform it properly.

INPUT:

EXPECTED OUTPUT:

It will be great if somebody can solve this and explain step by step.
Thanks in advance!

[rajendraongole1 · Accepted solution]

Hi @Imagauthamam - In  Power Query Editor and achieve the expected output as like below:

Mcode:

let
// Load the source data
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRRitWJVvJLzE0FM3zykxNLMvPzwBxDMBmcmAuVykuBShhBJErzUhKLIPpTyxUi84uywRxjMOmYAtXnkpqTkakUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),

// Change column type
ChangedType = Table.TransformColumnTypes(Source, {{"Column1", type text}}),

// Add Index Column
AddIndex = Table.AddIndexColumn(ChangedType, "Index", 0, 1, Int64.Type),

// Create a Custom Column for Type
AddedCustom = Table.AddColumn(AddIndex, "Type", each if Number.Mod([Index], 3) = 0 then "ID" else if Number.Mod([Index], 3) = 1 then "Name" else "Location"),
#"Pivoted Column" = Table.Pivot(AddedCustom, List.Distinct(AddedCustom[Type]), "Type", "Column1"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
#"Removed Top Rows" = Table.Skip(#"Removed Columns",1),
#"Filled Down" = Table.FillDown(#"Removed Top Rows",{"ID", "Name", "Location"}),
#"Removed Duplicates" = Table.Distinct(#"Filled Down"),
#"Removed Duplicates1" = Table.Distinct(#"Removed Duplicates", {"Name"}),
#"Removed Top Rows1" = Table.Skip(#"Removed Duplicates1",1)
in
#"Removed Top Rows1"

Hope this helps

[Ashish_Mathur · Accepted solution]

Hi,

Can be done in a single step

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    Custom1 = Table.FromRows(List.Split(Table.Skip(Source,3)[Column1],3),Table.FirstN(Source,3)[Column1])
in
    Custom1

Hope this helps.

[Imagauthamam]

Thank you so much @rajendraongole1 .
This works like a gem

[rajendraongole1 · Accepted solution]

Hi @Imagauthamam - In  Power Query Editor and achieve the expected output as like below:

Mcode:

let
// Load the source data
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRRitWJVvJLzE0FM3zykxNLMvPzwBxDMBmcmAuVykuBShhBJErzUhKLIPpTyxUi84uywRxjMOmYAtXnkpqTkakUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),

// Change column type
ChangedType = Table.TransformColumnTypes(Source, {{"Column1", type text}}),

// Add Index Column
AddIndex = Table.AddIndexColumn(ChangedType, "Index", 0, 1, Int64.Type),

// Create a Custom Column for Type
AddedCustom = Table.AddColumn(AddIndex, "Type", each if Number.Mod([Index], 3) = 0 then "ID" else if Number.Mod([Index], 3) = 1 then "Name" else "Location"),
#"Pivoted Column" = Table.Pivot(AddedCustom, List.Distinct(AddedCustom[Type]), "Type", "Column1"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
#"Removed Top Rows" = Table.Skip(#"Removed Columns",1),
#"Filled Down" = Table.FillDown(#"Removed Top Rows",{"ID", "Name", "Location"}),
#"Removed Duplicates" = Table.Distinct(#"Filled Down"),
#"Removed Duplicates1" = Table.Distinct(#"Removed Duplicates", {"Name"}),
#"Removed Top Rows1" = Table.Skip(#"Removed Duplicates1",1)
in
#"Removed Top Rows1"

Hope this helps