Excel BI Power Query Challenge Workout 076

Workouts Excel BI Challenge
1

-CAN YOU SOLVE THIS - POWER QUERY CHALLENGE 76-
(Solution in any language also welcome for Power Query Challenges)

Generate the result table from problem table

Download Practice File - https://lnkd.in/dkqu95gD

(Post answers in Comment. Your approach need not be different from others as long as you have worked out your approach independently)

#powerquerychallenge, #daxchallenge, #m, #dax, #powerbi, #powerquery, #powerpivot, #sheets, #googlesheets, #data, #analytics, #businessintelligence, #finance, #excel, #excelchallenge, #python, #r

image
image800×363 109 KB

LinkedIn Post

2

LinkedIn Post by:
Oscar Mendez Roca Farell

hi everyone, my proposal solution:

=HSTACK(A1:A10, REDUCE(HSTACK("Year-Quarter", TOROW(UNIQUE(B2:B10))), UNIQUE(A2:A10), LAMBDA(i, x, VSTACK(i, HSTACK(TOCOL(IF(LEFT(B1:G1)="2",B1:G1,NA()), 3), CHOOSEROWS( TRANSPOSE( FILTER(B2:G10, A2:A10=x)), {2,4,6}))))))

3

LinkedIn Post by:
Bo Rydobon

M-Code 
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Pop = Table.Combine([Table.Group](http://table.group/)(Source, "Country", {"T", (t)=> Table.FromRecords(List.Transform(List.Split(List.Skip(Table.ToColumns(t)),2),each Record.FromList(_{1},_{0}))) })[T]),
CY = Table.ExpandListColumn(Table.AddColumn(Table.Distinct(Source[[Country]]),"Y", each List.Alternate(Table.ColumnNames(Source ),1,1)),"Y"),
Combine = Table.FromColumns(Table.ToColumns(CY)&Table.ToColumns(Pop),{"Country","Year-Quarter"}&Table.ColumnNames(Pop))
in
Combine
4

LinkedIn Post by:
Bo Rydobon
 

=LET(z,A2:G10,h,A1:G1,b,INDEX(z,,2),c,UNIQUE(TAKE(z,,1)),y,FILTER(h,LEFT(h)="2"),d,TOCOL(IFNA(c,y)),
VSTACK(HSTACK(INDEX(h,1),"Year-Quarter",TOROW(UNIQUE(b))),
HSTACK(d,TOCOL(IFNA(y,c)),WRAPCOLS(TOCOL(--SORTBY(z,XMATCH(b,b)),3),ROWS(d)))))
5

LinkedIn Post by:
Alejandro Simón

Adjunto mi query... 
let
  Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
  Unpivot = Table.UnpivotOtherColumns(Source, {"Country"}, "Attribute", "Value"),
  NewCol = List.Skip(Unpivot[Value])&{null},
  Select = Table.SelectRows(Table.FromColumns(Table.ToColumns(Unpivot)&{NewCol}, {"Country", "Year-Quarter", "Col1", "Col2"}), each not Text.StartsWith([#"Year-Quarter"], "Column") ),
  Sol = Table.Sort(Table.Pivot(Select, List.Distinct(Select[Col1]), "Col1", "Col2"), {each List.PositionOf(Source[Country], [Country]), "Year-Quarter"})
in
  Sol
6

LinkedIn Post by:
Alexis Olson
 

let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  Unpivot = Table.UnpivotOtherColumns(Source, {"Country"}, "Year-Quarter", "Column"),
  ExtractValue = Table.AddColumn(Unpivot, "Value", each if Value.Type([Column]) = type text then null else [Column]),
  FillUp = Table.FillUp(ExtractValue,{"Value"}),
  FilterRows = Table.SelectRows(FillUp, each ([Value] <> [Column])),
  Pivot = Table.Pivot(FilterRows, List.Distinct(FilterRows[Column]), "Column", "Value", List.Sum)
in
  Pivot
7

LinkedIn post by:
Zoran Milokanović

Generating the Result table w/ #powerquery. #bitanbit #powerbi

let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
UnpivotedOtherColumns = Table.UnpivotOtherColumns(Source, {"Country"}, "Attribute", "Value"),
KeepValues = Table.AlternateRows(UnpivotedOtherColumns,0,1,1)[Value],
KeepOtherValues = Table.ToColumns(Table.AlternateRows(UnpivotedOtherColumns,1,1,1)),
CombineData = Table.FromColumns(KeepOtherValues & {KeepValues}, {"Country", "Year-Quarter", "Attribute", "Value"}),
PivotedColumn = Table.Pivot(CombineData, List.Distinct(CombineData[Attribute]), "Attribute", "Value", List.Sum),
SortedRows = Table.Sort(PivotedColumn,{{each List.PositionOf(List.Distinct(Source[Country]), _[Country]), Order.Ascending}, {"Year-Quarter", Order.Ascending}})
in
SortedRows
8

LinkedIn post by:
Eric Laforce

PQ proposal in 2 main steps
(1)=GroupBy “Country” + Transform to table for each
(2)=Expand Result-tables

let
Source = Excel.CurrentWorkbook(){[Name="tData76"]}[Content],
YQ_Names = List.Alternate(Table.ColumnNames(Source),1,1),
Group = [Table.Group](http://table.group/)(Source, {"Country"}, {"All", each let
_Cols=List.Skip(Table.ToColumns(_)),
_Zip=[List.Zip](http://list.zip/)({List.Alternate(_Cols,1,1,1), List.Alternate(_Cols,1,1), YQ_Names}),
_ToRecord = List.Transform(_Zip, each Record.AddField(Record.FromList(_{1}, _{0}), "Year-Quarter", _{2}))
in Table.FromRecords(_ToRecord)
}),
Expand = Table.ExpandTableColumn(Group, "All", {"Year-Quarter", "Population", "Male", "Female"})
in
Expand
9

LinkedIn Post by:
Sunny Baggu
 

=LET(
    _input, A2:G10,
    _country, TAKE(_input, , 1),
    _yrqtr, TOCOL(IFNA(TOROW(B1:F1, 1), UNIQUE(_country))),
    _tbl, WRAPROWS(TOCOL(DROP(_input, , 1)), 2),
    _header, TOROW(UNIQUE(DROP(_tbl, , -1))),
    _col1, DROP(_tbl, , -1),
    _col2, DROP(_tbl, , 1),
    _val, DROP(
        REDUCE("", _header, LAMBDA(a, v, HSTACK(a, FILTER(_col2, _col1 = v)))),
        ,
        1
    ),
    VSTACK(
        HSTACK({"Country", "Year-Quarter"}, _header),
        HSTACK(_country, _yrqtr, _val)
    )
)
10

LinkedIn Post by:
Sunny Baggu
 

=LET(
    _input, A2:G10,
    _country, TAKE(_input, , 1),
    _ucountry, UNIQUE(_country),
    _tbl, WRAPROWS(TOCOL(DROP(_input, , 1)), 2),
    _val, WRAPROWS(DROP(_tbl, , 1), 3),
    _res, DROP(
        REDUCE(
            "",
            _ucountry,
            LAMBDA(a, v, VSTACK(a, TRANSPOSE(FILTER(_val, _country = v))))
        ),
        1
    ),
    HSTACK(
        _country,
        REDUCE(TOCOL(B1:F1, 1), SEQUENCE(2), LAMBDA(a, v, VSTACK(a, TOCOL(B1:F1, 1)))),
        _res
    )
)
11

Linked In Post by:
Victor Wang
 

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
getTables = List.Transform(List.Split(List.Skip(Table.ColumnNames(Source)),2), each [Table.Group](http://table.group/)(Table.AddColumn(Table.SelectColumns(Source, {"Country"} & _), "Year-Quarter", (a)=> _{0}), {"Country", "Year-Quarter"}, {{"all", each Table.PromoteHeaders(Table.Transpose(Table.RemoveColumns(_, {"Country", "Year-Quarter"})))}})),
Expand = Table.ExpandTableColumn(Table.Combine(getTables), "all", {"Population", "Male", "Female"}),
Sort = Table.Sort(Expand, {each List.PositionOf(Expand[Country], [Country]), "Year-Quarter"})
in
Sort
12

LinkedIn Post by:
Caroline Blake
 

=LET(a,A2:G10,
_v1,TRANSPOSE(WRAPCOLS(CHOOSECOLS(a,3),3)),
_v2,TRANSPOSE(WRAPCOLS(CHOOSECOLS(a,5),3)),
_v3,TRANSPOSE(WRAPCOLS(CHOOSECOLS(a,7),3)),
t,VSTACK(HSTACK(SEQUENCE(3),UNIQUE(CHOOSECOLS(a,1)),BYROW(_v1,LAMBDA(x,1)),BYROW(_v1,LAMBDA(x,$B$1)),_v1),HSTACK(SEQUENCE(3),UNIQUE(CHOOSECOLS(a,1)),BYROW(_v1,LAMBDA(x,2)),BYROW(_v2,LAMBDA(x,$D$1)),_v2),HSTACK(SEQUENCE(3),UNIQUE(CHOOSECOLS(a,1)),BYROW(_v1,LAMBDA(x,3)),BYROW(_v3,LAMBDA(x,$F$1)),_v3)),
s,SORTBY(t,CHOOSECOLS(t,1),1,CHOOSECOLS(t,3),1),VSTACK(HSTACK("Country","Year-Quarter","Population","Male","Female"),CHOOSECOLS(s,2,4,5,6,7)))
13

LinkedIn post by:
Taeyong Shin
 

=LET( Data, A2:G10,h, A1:G1,c, INDEX(Data, , 2), v, WRAPCOLS(TOCOL(SORTBY(FILTER(Data, ISNUMBER(TAKE(Data, 1))), XMATCH(c, c))), ROWS(c)),u, UNIQUE(c),VSTACK(HSTACK(T(h), "Year-Quarter", TOROW(u)), HSTACK(TAKE(Data, , 1), TOCOL(IFNA(FILTER(h, LEFT(h, 3) = "202"), u)), v)))
14

LinkedIn Post by:
Luan Rodrigues
 

let
Fonte = Tabela1,
gp = [Table.Group](http://table.group/)(Fonte, {"Country"}, {{"Contagem", each
[
a = Table.UnpivotOtherColumns(_, {"Country"}, "Year-Quarter", "Valor"),
b = Table.AddColumn(Table.AddIndexColumn(a, "Ind",0,1),"Personalizar",each a{[Ind]+1}[Valor]),
c = Table.RemoveColumns(Table.SelectRows(b, each Text.Contains([#"Year-Quarter"],"Q" ) ),{"Ind"}),
d = Table.Pivot(c, List.Distinct(c[Valor]), "Valor", "Personalizar")
][d]}}),
res = Table.ExpandTableColumn(gp, "Contagem", List.RemoveFirstN(Table.ColumnNames(gp[Contagem]{0}),1) )
in
res
15

LinkedIn Post by:
Omid Motamedisedeh

M Code: 
let
 Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
 Solution = [A=Table.RenameColumns(Table.RemoveColumns(Source,{"2022-Q4", "2023-Q1"}),{{"2022-Q3", "X"}, {"Column1", "2022-Q3"}, {"Column2", "2022-Q4"}, {"Column3", "2023-Q1"}}), B= Table.UnpivotOtherColumns(A, {"Country", "X"}, "Attribute", "Value"), C= Table.Pivot(B, List.Distinct(B[X]), "X", "Value", List.Sum)][C]
in
 Solution
16

LinkedIn post by:
محمد حلمي

#1 
=LET(
v,TOCOL(IFNA(B1:F1,B2:B10)),
HSTACK(A2:A10,TAKE(FILTER(v,v>0),
ROWS(B2:B10)),WRAPCOLS( TOCOL(SORTBY(HSTACK(C2:C10,E2:E10,G2:G10),
TOCOL(SEQUENCE(3)^0+SEQUENCE(,3)-1))),9)))
17

LinkedIn post by:

محمد حلمي

#2 
=LET(b,B2:B10,r,ROWS(b),v,TOCOL(IFNA(B1:F1,b)),HSTACK(A2:A10,TAKE(FILTER(v,v>0),r),WRAPCOLS(TOCOL(SORTBY(FILTER(A2:G10,ISNUMBER(A2:G2)),MOD(SEQUENCE(r)-1,3)+1)),r)))
18

LinkedIn Post by:
Bo Rydobon
 

=LET(z,A2:G10,h,A1:G1,b,INDEX(z,,2),c,INT(COLUMNS(z)/2),w,WRAPCOLS(TOCOL(SORTBY(--z,XMATCH(b,b)),3),c*ROWS(UNIQUE(TAKE(z,,1)))),
u,UNIQUE(b),r,SEQUENCE(ROWS(w))-1,VSTACK(HSTACK(A1,"Year-Quarter",TOROW(u)),HSTACK(INDEX(z,(INT(r/c)+1)*ROWS(u),1),INDEX(h,(MOD(r,c)+1)*2),w)))

Result for 2 Quarters in 2022 without 2023-Q1 in Column F, G
Should be like this

image
image730×657 129 KB