Transformation Help Required in Power Query

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

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.