Power Query Transformation Request - Power Bi Request

[gssarathkumar]

I have  a below table as data source, which needs to be transformed as below:

Input:

S.No	Category	Name	Input1	Input2	KPI	Target	Deviation
1	Category1	Name1	Activity	Current progress to date
			100%	85%	15.00%	5%	10.00%
2	Category2	Name2	Planned Activity	Developed so far
			25	23	92.00%	100%	8.00%
3	Category3	Name3	Future Plans	Accepted Plans
			20	20	100.00%	95%	-5.00%
4	Category4	Name4	Required Skills	Actual Skills
			100%	10%	10.00%	90.0%	80.00%

Required Output:

S.No	Category	Name	Input1	Input1 Value	Input2	Input2 Value	KPI	Target	Deviation
1	Category1	Name1	Activity	100%	Current progress to date	85%	15.00%	5%	10.00%
2	Category2	Name2	Planned Activity	25	Developed so far	23	92.00%	100.00%	8.00%
3	Category3	Name3	Future Plans	20	Accepted Plans	20	100.00%	95%	-5.00%
4	Category4	Name4	Required Skills	100%	Actual Skills	10%	100.00%	90.0%	80.00%

Can someone please help me on this? The challenge that I am facing is the input is in first row and input value is in anothe row and also there is an empty column between each line items.

@amitchandak , @Greg_Deckler @lbendlin , @DesktopOwl @Goodlytics4U @Anonymous 

[gssarathkumar]

Hello All,

I got the required output, thanks for your support. However, there is a concern here.
The table contains two different data types. 1, Percentage and 2, Number.
After the table is transformed, I am not able to get the values with desired data type. Either I am getting in All Percentage values or All Number values. Is there a way for fixing this issue and use the values as in the source. Note: I need these values to be used for calculation in DAX.

@amitchandak , @Greg_Deckler @lbendlin , @DesktopOwl @Goodlytics4U @help Ahmedx @Ahmedx @lbendlin 

[lbendlin]

Use the dynamic measure format feature. Create dynamic format strings for measures in Power BI Desktop - Power BI | Microsoft Learn

[Ahmedx · Accepted solution]

pls try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZFNC8IwDIb/Shl4m9J9gR5F8Siix7FD0SjDus6uHezfm2ydVlARQt68TUsfkjwPoiAMVsLARemO6q24AenyaMq2NB21rdZQGVZrddHQNMwodsIn2HJRhHnwdBHnE5R5RjnKZoMdHO/d2/W3oE7sEcWOiHQnRVXBiXlka2hBqhoPG8XOQn8hijNKCaZF7HBGyD94Eo8ncTykG2usBkZYTT+wI9QGUcaDjyR8TAjgUBb9aKbZHyiph5I6FNI93G2p8e/DtZRyoDFWyJf/saiIe6tBHCz60bhdFQ8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [S.No = _t, Category = _t, Name = _t, Input1 = _t, Input2 = _t, KPI = _t, Target = _t, Deviation = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"S.No", Int64.Type}, {"Category", type text}, {"Name", type text}, {"Input1", type text}, {"Input2", type text}, {"KPI", type text}, {"Target", type text}, {"Deviation", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"S.No", "Category", "Name", "Input1", "Input2", "KPI", "Target", "Deviation"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"S.No", "Category", "Name"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Input1] <> null)),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"S.No", "Category", "Name"}, "Attribute", "Value"),
    #"Replaced Value1" = Table.ReplaceValue(#"Unpivoted Other Columns",each [Attribute],each if Text.Contains([Attribute],"input", Comparer.OrdinalIgnoreCase) and  Value.Is(Number.From(Text.Replace([Value],"%","")), Number.Type) then [Attribute]&" Value" else [Attribute] ,Replacer.ReplaceText,{"Attribute", "Value"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value1", List.Distinct(#"Replaced Value1"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

[lbendlin]

Generally speaking your data is not in a usable format.

Anyway, here's another approach:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "ldHNCsIwDADgVykDb1O6P3BHUTyK6HHsUDTKsK6zawXf3mTrtChMPDRp+gMfSVEEURAGS2HgrPSD9htxBcqLg6nulXnQtdUaasMarc4a2pYZxY74Ba/Ya5Vh4VVhEHE+wTTPKEbZrC/7infV54/vRS9izxc7H+WtFHUNR+Y5V3AHqRo8bBU7CT3qizMKCYY8driB/Icu8XSJ01FeW2M1MEK2XTMP0BiEDQcjLj4E5DhY3rVtmv0BSz1Y6mCUd3CzlUbJ/lJJ2duMFfJd/xxpxL0hIg43XdvcVMsn",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [
        S.No = _t,
        Category = _t,
        Name = _t,
        Input1 = _t,
        Input2 = _t,
        KPI = _t,
        Target = _t,
        Deviation = _t
      ]
  ),
  #"Filtered Rows" = Table.SelectRows(Source, each ([Input1] <> " ")),
  #"Replaced Value" = Table.ReplaceValue(
    #"Filtered Rows",
    " ",
    null,
    Replacer.ReplaceValue,
    {"S.No"}
  ),
  #"Filled Down" = Table.FillDown(#"Replaced Value", {"S.No"}),
  #"Merged Queries" = Table.NestedJoin(
    Table.SelectColumns(
      Table.SelectRows(#"Filled Down", each ([Category] <> " ")),
      {"S.No", "Category", "Name", "Input1", "Input2"}
    ),
    {"S.No"},
    Table.SelectColumns(
      Table.SelectRows(#"Filled Down", each ([KPI] <> " ")),
      {"S.No", "Input1", "Input2", "KPI", "Target", "Deviation"}
    ),
    {"S.No"},
    "Removed Other Columns",
    JoinKind.LeftOuter
  ),
  #"Expanded Removed Other Columns" = Table.ExpandTableColumn(
    #"Merged Queries",
    "Removed Other Columns",
    {"Input1", "Input2", "KPI", "Target", "Deviation"},
    {"Input1 Value", "Input2 Value", "KPI", "Target", "Deviation"}
  ),
  #"Reordered Columns" = Table.ReorderColumns(
    #"Expanded Removed Other Columns",
    {
      "S.No",
      "Category",
      "Name",
      "Input1",
      "Input1 Value",
      "Input2",
      "Input2 Value",
      "KPI",
      "Target",
      "Deviation"
    }
  )
in
  #"Reordered Columns"